Get Data from Multiple Excel Files with Different Sheet Names into Power BI

The ability to use Get Data from Folder in Power BI is a very useful option. However, if your sheet names are different in the list of Excel files, then you will face a problem. In this blog article, I’ll explain a method you can use that works regardless of the sheet names.

Sample Dataset

I have a set of Excel files like below;

Each file has the same structure of the data, but their sheet name is different;

Problem with Get Data from Folder

If you use the “Get Data from Folder” option in Power BI Desktop and Combine the Files, you would get the below results:

The Combine process, asks one of the files to be a template, and from that template, I need to choose the Sheet Name that contains the data which I want to be combined across all files.

Because in my files, this sheet name only exists in one file, it means the combined result would only work for one of the file, and not the others!

Solution 1: Use Sheet Index Not the Name

The first solution to use for the problem above is that you can use the index of the sheet, which means the order of the sheet among other sheets. for example in the scenario below, that order can be like this (start from zero on the left side).

Now that you know the sheet index, you can fix the function that is automatically created by the Power Query when you combined files. This should be under a folder and named something like “Transform Sample File”.

Note that the above query can be found in Power Query Editor, which is accessible using “Transform Data” in the Power BI Desktop.

After finding the query, go and find the Navigation step, with selecting this step, you will see the formula bar’s expression. Enable the formula bar in the View tab if you cannot see it in the editor.

This step, sometimes might be called other things and you might need to check the 2nd or the 3rd step after the Source to find it.

Now use the Sheet index inside the {} instead of anything written there.

The code should looks like this now;

This action should also automatically fix the function associated with that query, and as a result your combined result should be all working without any errors;

Caution 1: Remove Unnecessary Items

The method above might not work if you have other items in addition to the sheet, such as a table or anything else. so make sure to filter the Kind field in the Source tab to only Sheets (or anything else you are looking for);

Caution 2: Remove Unnecessary Steps

Sometimes, there is a little difference in the content of each Excel file, and you might have a column or two that do not exists in other files. You need to scan the list of steps, and remove any step that has something hardcoded with the column names in it, for example; remove Changed Type steps.

Solution 2: Filter to the Name Template

If the sheet you are looking for is not always at the same position (index) in all the files, then find other alternatives. For example, try to find a name template. Let’s say, they all start with 20…., such as 201506, 201507….201601…

Then if you filter it for that name, then it is very likely that the only sheet with that name template would be in the result set which is then accessible with sheet index 0.

To filter the sheet names based on a template you can use this approach:

Insert the new step, and then enter the characters you expect in all sheet names;

Now in the result set, it is very likely that you have one sheet only, which can be accessed using the sheet index zero;

You can also use any other combinations of text filters, such as Contains, ends with etc.

Download Sample Power BI File

Download the sample Power BI report here:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.