Applying filter in Excel is a very common task. We used to filter a dataset and focus on a subset of data that we are interested in. It is super easy to filter. The problem is, are you sure you have the right dataset being filtered?
Situation
I have a list of files stored in a folder. All files are generated automatically with the same filename convention: “FiscalYearWeekyyyyww.csv”, where yyyy = year; ww = week. The file started from week 01 of fiscal year 2018, all the way to the latest week. The latest file, say, is “FiscalYearWeek202105.csv”.
The task is to combine all files but those for FiscalYear 2018, using Power Query of course. (note this post is not about Power Query, but you may refer to other related post here).
Easy! We can exclude files of fiscal year 2018 by filter rows where ‘Name’ does not contain 2018, as shown in the following screenshot.
If this is what you do, please pause for 10 seconds and think carefully…
Have you heard about all the amazing new functions/features like Dynamic Arrays in Excel 365 but not sure what they are?
Or you have no idea at all how Excel 365 is different from standalone versions of Excel?
If you answer “Yes” to any of these questions, I suggest you take some time to read the blogpost Personal Stock Portfolio Dashboard by MYNDA TREACY , and more importantly watch the video there.
Why?
Because you will see how to build such a beautiful Excel Dashboard in less than 30 minutes using some of the new features available in Excel 365.
Personally, I think this is an excellent video demonstrating some core new features like Excel Data Types and Dynamic arrays. Not to mention how easy an Excel Dashboard can be built in 30 minutes (when you get the skills required). 😁
At the end of the blogpost, you will also see more Excel Dashboard post by MYNDA. You will learn a lot of Excel skills, tricks and tips by reading those posts and watching the videos associated with them.
If you are planning to uplift your skills in Excel Dashboard, consider the popular Excel Dashboard Course by MYNDA TREACY. There is a 20% discountuntil Feb 11, 2021. 👍
So, do yourself a favor and check out the course. The price is incredibly fair, the course is awesome and it’ll transform your Excel reports and possibly even your Excel career. 😉
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.
Part 2 – What if we have inconsistent table structures across files?
Note: this is a long post. 😉
This is the continuation of previous post. Suggest you read that post first if you are new to Power Query. Moreover you may download sample data files there to follow along.
In the previous post, we used Power Query to combine all files in a folder. With that built, we can get new data file by a simple click of Refresh. The following screencast shows you that we have combined three data files. When a new file – Week 4 data, comes into the folder, we can easily get the new data by a click of Refresh.
Watch this:
Before Week 4 data, there were 63 rows of data. After we have 84 rows of data.
Isn’t it great? It is awesome! 😎
Then next week, we have data file for Week 5. We feel great as we expect a consolidated table by putting the data file into the same folder, followed by a click of Refresh… BUT…
we didn’t see the date of week 5, but (Blank)? What happened? 😨
Let’s have a closer look at the Queries & Connections Pane
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:
Modify the query to fit table structure for “new” files and manually change the headers in all old files for consistence or
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.
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.
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… 😑😅
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. 👻
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.
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.
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
specify the search condition to look for “partial” or “exact” match?
ask for “case sensitive” search?
look into values only (not formula)?
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. 😉
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 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!
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.
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.
Disclosure: The author earns a small amount of commission (at no additional cost to you) for the product/service successfully sold through the links of [Affiliate].
Wanna buy me a drink!?
Click below photo to my PayPal.Me
I appreciate it. :)
Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity
