Forum Discussion
Power Query Comination of Excel files with different sheets
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?
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
CombineSheetsHere is an GUI tutorial that might help
Solved: Combine multiple excel workbook with multiple shee... - Microsoft Fabric Community
1 Reply
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
CombineSheetsHere is an GUI tutorial that might help
Solved: Combine multiple excel workbook with multiple shee... - Microsoft Fabric Community