Combine files in a folder with Power Query in #Excel

Note: This is a long post… ๐Ÿ˜‰

Part 1 – What Power Query does for us in the process?

Combining data in different files in a folder is super easy with Power Query!

If you know about Power Query, you should know that combining files in a folder is a powerful yet simple task using Power Query, especially when there is a consistent pattern among all files. Like in the example above, there is only one worksheet “Sheet1” in each file. Moreover, all files share consistent layout with common column headers, which reside on row 1.

When new files come in week by week, a simple click of “Refresh” would get us the combined table for further analysis. As simple as that.

It worked like a charm until one day, your I.T. team changed the headers slightly… without getting consent from you (and all other users) ๐Ÿ™„

For whatever reason, the headers had been changed to below:

Excuse me? Breaking the headers from one row into two rows??? What’s the point of doing this? I have no idea but it happened. ๐Ÿ˜ญ

And you knew it… the query was broken.

The luckiest part is, the change was minor AND it’s confirmed that it will not changed anymore. Put it in other way, all the coming files will have two rows of headers, with the same data layout in the four columns in the same order in the future.

To fix the broken query, we could:

  1. Modify the query to fit table structure for “new” files and manually change the headers in all old files for consistence or
  2. Modify the query to fit the table structure for both new and old files

Which option would you go for?

If there is only one or two “old” files, I would probably pick option 1. What if there are tens of old files in place already? Option 2 is the preferred way. No doubt! ๐Ÿ˜

Either way, in order to modify the query for combining files, we need to know what to modify, and more importantly where to modify.

Therefore it’s better to understand what is happening when we try to combine files in a folder using Power Query. This will be the focus of this blogpost.

Continue reading
Posted in Power Query | Tagged | Leave a comment

Add emoji to your numbers ๐Ÿ˜ in #Excel

Is it possible in #Excel? Of course, as you have just seen it! ๐Ÿ˜

Indeed, it’s easier than you may have thought about. We can do it by custom format.

First of all, select the cells that you want to apply custom format and then right click…

or simply press Ctrl+1 to open the Format Cells dialog box:

Continue reading
Posted in Excel Tips, Format | Tagged | 2 Comments

Farewell 2020… Happy New Year!!

2020 comes to an end in just a few hours . I guess itโ€™s the first time majority of people shares the same new year wish.

It was a though year. Keep in faith! Tomorrow will be a better day!

Wish you all a healthy and happy new year of 2021!

Posted in General | Leave a comment

Quick Tip – Close all #Excel workbooks at once

Have you ever had more than 10 workbooks opened at the same time?

Did you spend too much time to close them all one by one? If so, you will love this simple trick to close all workbooks simultaneously!

The trick is to hold Shift key while clicking the “X” on any opened workbook.

Let’s watch it in action:

p.s. Remember to save changes to your work before closing. ๐Ÿ˜‰

Posted in Excel Tips | Tagged | Leave a comment

Dense Ranking with Power Query – Unexpected Behavior and Workarounds

This post is inspired by the article – Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub.

The first part of this post is a summary of what I read from Philip’s post. While the second part is an alternate way to solve the problem, using just a slightly different approach.

What is Dense Rank?

Again, a picture tells thousand words. What we want to achieve is to add a column of “Dense Rank” according to the scores by course. See below:

It could be achieved by creating a “helper table” with unique scores by course in descending order.

The straightforward way is to load the table into Power Query, and then sort the “Course” in ascending order; followed by “Score” in descending order.

Continue reading
Posted in Excel Tips | Tagged , , | 2 Comments

Hide and Seek in #Excel – PivotTable Fields

Have you ever encountered a situation like this? How are we supposed to work with a PivotTable without seeing the data fields?

I was so lost when I saw this from a workbook on my friend’s notebook. He came to me for PivotTable help but I was not able to even work with the PivotTable he opened and presented to me… How embarrassing… ๐Ÿ˜‘๐Ÿ˜…

Continue reading
Posted in Excel Tips | Tagged , | Leave a comment

Replace values in filtered ranges using Copy and Paste trick in #Excel

Situation:

In a filtered range of data, we made few changes on the side. Then we want to replace the original values with the updated values.

What action appear on top of your mind? Copy and Paste of course.

But… if you had tried, you knew it… a regular Copy and Paste does NOT work. ๐Ÿ˜“

Did you see the problem?

Continue reading
Posted in Excel Tips | Tagged , , | Leave a comment

Have you encountered any weird formula in #Excel?

Happy Halloween! ๐Ÿ‘ป

Let’s do something fun.

You are invited to leave your comments below. For what? For sharing with us any “weird” formula that you have ever seen in Excel. Real cases please. ๐Ÿ˜

Wait… what do I mean “weird”?

Let’s define it in this way:

A weird formula is one that is written in a “strange” manner. It could be the uses of functions, or simply the way it is written.

Here’s my examples:

=SUM(VLOOKUP(A1,D1:F10,2,false))
=SUM(A1+A2+A3)
=SUM(A1*A2)

The above examples are real. I received a workbook where all formula are wrapped with SUM, for no particular reasons. All formula work perfectly fine without SUM.

So now is your turn. What weird formula you’d encountered? Please leave your comments. ๐Ÿ‘ป

Posted in Formula | Tagged | 1 Comment

Input data on one worksheet… Get summary on another worksheet in #Excel

It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one sheet for displaying summary for each month. In this way, a total of 13 worksheets will be displayed on your workbook. Indeed, this was a question to me…

Can it be done?

Of course it can be done. BUT we could do it doesn’t mean we should do it.

My suggestion is to hold only ONE sheet to display the monthly summary on which users have the flexibility to select any month (or any period) by using Pivot Table Timeline.

Here’s the layout of the proposed solution:

You may download a sample file to follow along.

Note: The file is saved as .xlsm (macro-enable) format. Please enable content when prompted if you wish to see the auto-refresh action.

Continue reading
Posted in Excel in Action | Tagged , , | Leave a comment

SUM the last X non-blank values in a column in #Excel

Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He showed me the above layout and asked for the formula to SUM and AVERAGE the last 10 records of each player…โ€ฆ As he called me Excel guru, there is no reason I do not help him. ๐Ÿ˜

For demonstration purpose, I change the last 10 records to 5. Indeed, I have assigned a variable (from 3-10) for user’s selection. The expected result is to obtain the SUM and AVERAGE of the highlighted cells, shown below:

And new records will come from time to time. That means the table will expand. The best way to deal with it is of course (not whole-column-referencing but) turning the data range into Excel Table.

You may download a sample file to following along.

Continue reading
Posted in Excel in Action, Formula | Tagged , , , | 2 Comments

Understanding Find and Replace in #Excel

Find and Replace is a very handy tool in Excel. It helps you to find a value quickly. You may then replace the value found with another value, either one by one or replace all at one batch.

To use the basic of Find and Replace, it requires no special reading as it is quite straight-forward. Perhaps the most practical tip to regular user is the shortcut keys:

  • Ctrl+F ==> Find
  • Ctrl+H==> Find and Replace

However, did you know… you can also

  1. specify the search condition to look for “partial” or “exact” match?
  2. ask for “case sensitive” search?
  3. look into values only (not formula)?
  4. even search for a format?

If you are eager to learn more about Find and Replace, and hence boost your productivity with Excel, please continue to read this article. ๐Ÿ˜‰

You may download a simple file to follow along.

Continue reading
Posted in Excel Tips | Tagged , , | Leave a comment

#Excel #PowerQuery Challenge – Add value to CSV data in cells

As always, a picture tells thousand words. The above image explains clearly what we want to achieve, with Power Query in Excel of course.

This problem was bought to my attention when I watched the Dueling Excel Podcast #192 by MrExcel.com and ExcelIsFun channels. You may check it out here:

@MrExcel.com channel: https://www.youtube.com/watch?v=pcnWsCwEPbE
@ExcelIsFun channel: https://youtu.be/Z2Cy9IY7IBA

MrExcel solved the problem with VBA while ExcelIsFun solved it with LET functions. Super cool techniques. However it requires the latest version of Excel with Dynamic Arrays. What if you don’t have Dynamic Arrays? Power Query should be the best alternative. I was a bit surprised that they didn’t solve the problem with Power Query. So I couldn’t wait to try on my own and you know what… Yes, it can be solved with Power Query!

Continue reading
Posted in Power Query | Tagged , , | Leave a comment

Learn how to build effective #Excel Dashboard from Mynda Treacy

Did you know… you can create such a beautiful dashboard using Excel?

Did you also know… this visually appealing dashboard was built in just more than 30 minutes?

No kidding! Check it out HERE and you will see.

Want to elevate your Excel skills??

Excel MVP – Mynda Treacy offers a wide range of Excel course online @MyOnlineTrainingHub where you can learn practical Excel skills at your own pace.

And from now through August 20, there will be 20% off for both Excel Dashboards and Power BI courses. So what you are waiting for? ๐Ÿ˜‰

Disclosure: I make a small commission (at no additional costs to you) for students who join Myndaโ€™s course via my site, but as you know I donโ€™t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesnโ€™t live up to what Iโ€™ve promised youโ€™ll think poorly of me too and I donโ€™t want that. Oh, and just watching the course videos wonโ€™t transform your career, you have to actually put it into practice, as if reading a cookbook wonโ€™t make you a chef.

Posted in General | Tagged , | Leave a comment

Combine all visible worksheets from multiple #Excel files in a folder

It is a common task for us to combine all worksheets in a workbook. It could be a time consuming task without Power Query. With Power Query, it’s piece of cake. ๐Ÿ˜

Wait? What about if you want to combine all visible worksheets from all Excel files in a folder? It requires a little more Magic from Power Query but it’s totally achievable without deep-diving into M code… just touching the surface would do. ๐Ÿ˜‰

Let’s watch it in action:

You may download the sample files HERE to follow along:

(note: suggest you put the Q4 file aside for “refresh” later)

Step-by-Step instructions with screenshots

If you prefer reading to watching, please continue to read the step-by-step instructions below.

Continue reading
Posted in Power Query | Tagged , , , | Leave a comment

Quick Tip – Probably the quickest way to open txt file with #Excel

Are you tired of seeing the (Text Import) Wizard ๐Ÿง™โ€โ™‚๏ธ every time you open txt file with Excel?

In this super short video, you will see probably the quickest way to open a txt file with Excel.ย  Enjoy! ๐Ÿ˜‰

Posted in Excel Tips | Leave a comment

Hide and Seek – Scroll Bars and Worksheet Tabs in #Excel

Did you encounter this?ย  All the worksheet tabs and scroll bars are gone in your Excel workbook!!!

Excel Tip - Hide and Seek Scroll bars and worksheet tabs

๐ŸŽถ๐ŸŽถ Tell me… how am I supposed to work without you…๐ŸŽถ๐ŸŽถ

 

No worries!ย  They are not gone.ย  They are just hidden (by someone somehow ๐Ÿค”).

Let’s get them back to work!ย  Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Lovely stickers in #Excel 365

Do you use stickers when you use communication apps like WhatsApp?ย  I guess that’s almost a must-have features right now for communication apps.

Would it be nice if you can use stickers in Excel too?

Excel Tips - Insert Stickers

Yes… you can, if you are using Excel Microsoft 365.

Excel365

Go to View -> Insert -> Icons -> Stickers

Excel Tips - Insert Stickers1

Explore it and have fun!

 

Learn more here:

https://support.microsoft.com/en-us/office/insert-images-icons-and-more-in-microsoft-365-c7b78cdf-2503-4993-8664-851085c30fce

Posted in Excel Tips | Tagged , | Leave a comment

Hide and Seek – Formula Bar, Column and Row Headers

Have you ever experienced a shocking moment like this in #Excel?

Excel Tip - Display the formula bar and column row headers

We are so getting used to see the formula bar, Column and Row headers that we take it for granted.ย  They should always be there for me…. aren’t they?

What’s wrong with my Excel worksheet? ๐Ÿ˜จ Continue reading

Posted in Excel Tips | Tagged , , , | Leave a comment

Should I link to other workbooks…

…when writing #Excel formula

Note: This post is not about “security” issue ๐Ÿคทโ€โ™‚๏ธ

 

Is the following message familiar to you?

Excel Tip - External links

I wonder… when you see this message, which button would you click?ย  Please let me know by leaving comments below.

From my observation, most people would click “Don’t Update” when they are dealing with a workbook that was prepared by others.

Why is that?

Because most of the time they don’t have access to a “workbook” that is saved in someone else PC.ย  Even the workbook (the external link) is saved in a shared folder, are you sure your counterparts have access right to that shared folder?

If a user cannot “update” the value the first (few) time they opened the file, he/she would have a tendency to click “Don’t Update” the next time they open the same file…. and worst still, he/she builds a habit of “Don’t Update” every time they see the message, which is super dangerous as he/she may not be working with the updated data that they need.

So, the question is:

Shall I link to external workbooks when writing Excel formula?

Continue reading

Posted in Excel Tips | Tagged | Leave a comment

Quick tip – Zoom in and out of the #PowerQuery editor

In both #Excel and #PowerBI

Did you know… we can ZOOM IN or zoom out everything but ribbon in the Power Query Editor in both Excel and Power BI Desktop…

Zoom InOut in the PQ Editor in Excel

… by pressing

  • CTRL+SHIFT+=
  • CTRL+SHIFT+-

respectively.

The story behind the discovery

Continue reading

Posted in Excel Tips, Power BI Desktop, Power Query | Tagged , | Leave a comment