Power Query Comination of Excel files with different sheets

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