Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Shihab
New Member

Combine 10 excel files in a folder, each excel file has 50 worksheets

How to combine 10 excel files in a folder, each excel file has 50 worksheets, each worksheet has same name among the 10 excel files and the same number of columns and the same column headers, I can do the combination for one worksheet at a time, my gaol is to load these 50 sheets in power bi model in one step since it is not practical to do in 50 times one by one.

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

This article shows an example of how to do that. 

How To Import Multiple Files With Multiple Sheets In Power Query | How To Excel

 

Pat

Microsoft Employee

Many Thanx “ppm1” for you reply, I knew these steps, but my request is different, let me give more explanation... I have 10 excel files all have same structure in terms of number of worksheets and in columns headers, each file have 50 tabs (worksheets), I want to combine all 1st sheets of the 10 files together, then 2nd sheets, then 3rd sheets,.... till the 50th sheets, then I want to view them as 50 Tables in Power BI as below, these 50 sheets contans different data and diferent columns.

 

In below, I loaded the files 3 times using [Combine and Transform Data] to just combine the first 3 sheets to make only 3 tables, please advise how to generate the output of 50 Tables (Each Table is combination of 10 sheets)?

 

 Note that the size of the 10 files is 1GB and it is time consuming and not practical to do it 50 times like what I’m doing now.

 

Again.. The parallel sheets from the 10 files are the only sheets to be combined and the output should be 50 tables in power BI.

 

Shihab_0-1698517473405.png

 

 

 

If your goal is 50 tables, that will end up being 50 queries. To speed things up, you could write a custom function that takes the sheet name as input. Or you could use an R or Python script in Power Query, but having that many similar tables in not likely to be a good model. Is there a way to consolidate most of those tables together (and possibly end with 1 table)?

 

Pat

Microsoft Employee

-Thanx ppm1 for your feedback..

-Note that it is not possible to cobine the 50 sheets as one table, they have different structure in data and number of columns and number of rows. 

-Yes I need to do 50 queries, could you please advise how to do "custom function that takes the sheet name as input" can you please share a video or a steps for that, thats will be great..

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors