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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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;

A set of Excel files

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

Each file has a different sheet name

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:

Combining Files

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.

Sheet Name asked for the Sample File

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!

the process works only for the file that has the same sheet name

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).

Sheet Index

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”.

Finding the function’s query

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.

Find the navigation step

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.

Using Sheet index in the code

The code should looks like this now;

Sheet index zero represents the very first sheet in the Excel file

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;

Combined data is fixed now

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);

Filter to have only Sheets

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.

Remove steps that are hard-coding column names

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:

Filtering based on the sheet name starts with something

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

The first few characters that are repeated in all the 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;

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:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

4 thoughts on “Get Data from Multiple Excel Files with Different Sheet Names into Power BI

Leave a Reply

%d bloggers like this: