Get data from a folder of Excel worksheets with varying sheet names
I have found that Power Query is one of those tools that gets me 80% of what I need right out of the box with the built-in interface. But there is always that little detail or catch that has me diving into the M-Code to get across the finish line. This past week was one of those times.
As you may or may not know, you can use Power Query (for Excel or Power BI) to consolidate data from a folder of files that have the same structure. This is great for routine data appending for things like transaction date based system extracts.
For our example today, I will be using some made up Excel files that just have two columns. A date and a value. Each file has a different sheet name. “Jan”, “Feb”, “Mar”, and “Apr”.
I have 4 such files. One for each month, January through April. I have them saved in a folder on my computer.
I will use the Get Data → Folder option in Power BI to grab all the data for each of those files:
The first prompt asks me for the folder location:
The system now lists all the files in that folder. I will select Transform so we can do some more work with the files before it tries to load the data:
Since I don’t need to filter which files I’m loading or perform any other initial modifications, we will click the ‘Combine Files’ button to begin the process of extracting data from each of the files:
Here is where the hurdle appears. The system checks the first file to determine the structure and layout of the files. In this case it chose the April file. I select the sheet named ‘Apr’ and it previews the data for me.
When I click OK and it begins reading the files, it quickly determines that all the files don’t have a sheet named ‘Apr’ so it gives us an error:
In order to correct this, we need to find out where it is choosing the sheet name and see what we can do to make this more dynamic.
The trick in this case is not to look in the M-Code for this query, but to review the helper function that was auto generated by the Folder import. In this case it is called “Transform File”. It’s M-Code looks like this:
There it is! It’s looking for the “Sheet” with name “Apr”. However, each of our files has a different name for Sheet 1. Luckily a quick web search provides us the modification that we need to make to that function in order to dynamically choose the first sheet.
We will replace [Item = “Apr”, Kind = “Sheet”] with the number 0 to represent the first sheet (zero based array).
Now, when we go back to our query, we see that it is able to pull in the data from all 4 of our sheets even though they have different sheet names:
I’m grateful for the Power Query team’s work on building such amazing helper interfaces. Within just a couple of minutes, you can be analyzing and visualizing data across hundreds of source files. Of course, those interfaces aren’t going to work for every single use case. However, I hope that this quick run through encourages you to keep digging when the tool doesn’t quite get you to the finish line. With a little bit of digging into the M-Code, we were able to go from “I guess it doesn’t work for my case” to “look ma, no hardcoded sheet names!”
