Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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"
Solved! Go to Solution.
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
Source
I 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
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
PromotedHeaders
Make 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.
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
Source
I 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!!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |