Note: This is a long post… 😉

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

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.