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

[Shihab]

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.

[ppm1]

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

[Shihab]

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.

[ppm1]

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

[Shihab]

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