Last week I was running a Power Query course. I was showing the delegates how to use Power Query to combine rows from multiple Excel files into a single table in a new file. In the demo, each file had a sheet called Sheet1 and that sheet contained the data to be combined.

But we all know that the real world isn’t like the demo world. In the real world the sheets containing the data to be combined may have different names. For example, you want to combine the data from 4 sheets that are in 4 Excel files:

  • File1 contains a sheet called “North Region Jan Sales”
  • File2 contains a sheet called “South Region Jan Sales”
  • File3 contains a sheet called “West Region Jan Sales”
  • File4 contains a sheet called “East Region Jan Sales”

As you can see the sheet names are not the same so the combine process won’t work. In this video I show you a workaround. My solution requires that the sheets to be combined are in the same position within each file (i.e. the first sheet or second sheet etc). The solution uses the Advanced Editor in Power Query to change a single line of M code. But don’t worry if you’re not a M-coder, the video takes you through the process step-by-step

You can download a copy of the files I used in the video by clicking through to YouTube. The link for the file is in the YouTube description.