Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically

Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. There is a very little but useful trick that you can use to get data from multiple sheets dynamically rather than manually one by one from each sheet. This post is about that technique.

Sample Excel file

I have a sample excel file with multiple sheets, and the sheets contain the same data structure, but the actual data rows are different. Here is the screenshot of that file;

Get Data from Excel

In Power BI Desktop, You can start by Getting Data from Excel;

In the Navigator window, you will see multiple sheets or even tables, all you need to do is just to select one of those (doesn’t matter which one). and then click on Transform Data.

Get the List of All Sheets

In the Query Editor window, right-click on the Navigation Step and choose Delete Until End (Note that this action will remove this step and all steps after with no undo option)

Now you should see only one step (named probably as Source), and the preview of data should be all Excel sheets and tables in that Excel file:

Filter Sheets

Sometimes, you want to select all sheets, sometimes, you might want to select tables only. Sometimes you might want to filter sheet names that have specific characters etc. All of those options are possible through the headers of each column;

Apply all the filterings that you need. In my case, I just want to select everything.

Expand

At this stage, we just need to keep the Data Column, and remove anything else (unless the name of the sheet is also a valid data that you want to process, which is not the case in my example);

Now you can expand the Data column to the underlying structure;

This action will combine the data from all sheets into one;

That’s it, all the data from multiple sheets are combined. You might need, however, to clean the data, remove duplicate headers, etc to have a clean combined table as a result.

Note that this solution is fully refreshable, either through a gateway, or without the gateway is the Excel file is located on a cloud data source. Here I explained, how you can refresh it without the gateway if the file is on OneDrive for Business.

Video

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

10 thoughts on “Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically

  • You can also combine this technique with the Folder source if you have multiple files, each with multiple sheets. The trick is to hack the Transform Sample File that Power Query generates for you. Apply the steps above to that query, and the main query that combines the files will now have data from all tabs in all files.

  • Best solution I’ve come across yet. Most result go through querying each sheet separately before appending them all, however this solution has just saved me a lot of time.

  • What would be the process if the file is located on Sharepoint? This process is amazing with local file, however I have been trying to apply it to Sharepoint file (same source file).

    Thank you very much!

  • Hi Reza,
    is it possible with this combination to keep also the name of the files that you are reading in a folder? so reading more than one sheet and also keeping the name of the file. Providing that all files have same format

    thanks!

    • Yes It is. You can get data from Folder, this will get all the files in that folder, and then use this method to combine all the sheets of all the files into one table.

Leave a Reply