Power Query Comination of Excel files with different sheets

[Anonymous]

Dear community, 

I have one structure excel file, which defines sheets and columns to be loaded. 

Then from Power BI Dataflows, I created lot´s of queries which combine the files in a folder.
Now, my new files that I need to load, have new excel sheets. Therefore, I would like to load those sheets as well. 

I know, that I need to add those sheets in the structuring file. Do also need to add the new sheet in all the other files? Is there a trick, so that I do not need to add those sheetsin all files?

[rubayatyasmin · Accepted solution]

Hey, @Anonymous 

PQ supports dynamic reading of multiple Excel file. You can use Table.Combine() 

Here is an example,

let
Source = Folder.Files("Path to your folder"),
ExcelFiles = Table.SelectRows(Source, each [Extension] = ".xlsx"),
CombineSheets = Table.Combine(
Table.TransformColumns(
ExcelFiles,
{"Content", each Excel.Workbook(_, true), type table}
)[Content]
)
in
CombineSheets

Here is an GUI tutorial that might help

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

Solved: Combine multiple excel workbook with multiple shee... - Microsoft Fabric Community