Forum Discussion
How to load multiple excel workbooks each with multiple worksheets?
Here is my scenario. I have a folder containing a number of excel workbooks, each containing one or more worksheets. The data is consistent throughout. I need to load every worksheet in every workbook. I have Power BI loading every workbook in the folder, but it only picks up the first worksheet in each. I believe my problem is in the transform file query, but I don't know how to fix it.
Below is what Power BI generated:
= (Parameter13 as binary) => let
Source = Excel.Workbook(Parameter13, true, true),
AchOnDemandDetailExport_Sheet = Source{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(AchOnDemandDetailExport_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"
Hi, yes It's one of those Helper Queries Power Query generated when you load multiple files from folders. It happens to me as well recently when i load parquet files from Synapse Data storage gen2.
The solution to your question would be modify a bit on your 'Transform File' function as below
let Source = (Parameter1) => let Source = Excel.Workbook(Parameter1, null, true), SheetsOnly = Table.SelectRows(Source, each [Kind] = "Sheet"), AllData = Table.Combine(SheetsOnly[Data]), Promoted = Table.PromoteHeaders(AllData, [PromoteAllScalars=true]) in Promoted in SourceI tested a little with similar files and it works. from there you can filter them down to your data only from all your worksheets.
Also, i came across this video about how to better deal with these Helper queries and i'm sharing this with you as well. hopefully it helps. https://www.youtube.com/watch?v=2TeLzgkv1H0&t=266s
4 Replies
Hi, yes It's one of those Helper Queries Power Query generated when you load multiple files from folders. It happens to me as well recently when i load parquet files from Synapse Data storage gen2.
The solution to your question would be modify a bit on your 'Transform File' function as below
let Source = (Parameter1) => let Source = Excel.Workbook(Parameter1, null, true), SheetsOnly = Table.SelectRows(Source, each [Kind] = "Sheet"), AllData = Table.Combine(SheetsOnly[Data]), Promoted = Table.PromoteHeaders(AllData, [PromoteAllScalars=true]) in Promoted in SourceI tested a little with similar files and it works. from there you can filter them down to your data only from all your worksheets.
Also, i came across this video about how to better deal with these Helper queries and i'm sharing this with you as well. hopefully it helps. https://www.youtube.com/watch?v=2TeLzgkv1H0&t=266s
The video was key! Thank you!!!
You're on the right track using a Folder connector, but you're correct — the issue lies in the Transform File logic. By default, Power BI grabs only the first worksheet (Source{0}), instead of looping through all sheets in each Excel file.
You need to replace Source{0} with logic that appends all sheets from the workbook.
Replace the function code with this:
(Parameter13 as binary) => let Source = Excel.Workbook(Parameter13, true), // Filter only sheets (optional, exclude hidden or system tables) SheetsOnly = Table.SelectRows(Source, each [Kind] = "Sheet"), // Expand all sheets into one table AllData = Table.Combine(SheetsOnly[Data]), // Promote headers (optional – assumes all sheets have headers) PromotedHeaders = Table.PromoteHeaders(AllData, [PromoteAllScalars = true]) in PromotedHeadersMake sure all worksheets have the same column structure to avoid column mismatch errors.
This didn't work, I am still only geting results from the first tab in the workbook.
