There are quite a few different common business scenarios where you get data in the form of a file on a regular basis. The 3 common patterns I use most often are.
- Each day/month/year you get a new file containing incremental data. All the data in all the files need to be combined and loaded. I have previously shared this article explaining how to combine all files in a folder with Power Query.
- Each day/month/year you get a new file and it replaces the old file. I wrote about this pattern in Load the Latest Version of a File with Power Query.
- You have a workbook with multiple sheets within a single workbook that need to be combined. This could be in addition to pattern 1 or 2 above. This is what today’s post is about.
Combine All Sheets in a Workbook with Power Query
There are a couple of tricky issues to overcome with this pattern.
- Write the query in such a way that new sheets are automatically loaded
- Handle the errors with repeating header rows coming from the multiple sheets
- Handle the renaming of the Sheet Name column so it works regardless if the sheet order changes.
I cover these tricks in my video below.
Note also the comment from Tristian Malherbe in the YouTube comment section. What Tristian is talking about is manually making a change to the code in the source step. My source step as created by the UI is as follows
= Excel.Workbook( File.Contents("C:\Users\Matt\Consolidate-Worksheets.xlsx"), null, true )
If you look at the documentation for this function, you find the following
The UI set useHeaders as null and delayTypes as true. By simply changing the first optional parameter to true (manually in the formula bar), Power Query will treat each sheet as having headers when they are combined removing the repeating header issue. Nice tip – thanks @Datatouille.
Want to Learn Power Query in a Structured Way?
My Power Query Online training course consists of 7.5 hours of online video training material that will teach you everything you need to know about using this fabulous tool for both Power BI and Excel. You can watch some free sample videos and find out more about registration following the link above.