Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join 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.

Reply
dirvine
New Member

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"

1 ACCEPTED SOLUTION
MasonMA
Responsive Resident
Responsive Resident

@dirvine 

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. 

MasonMA_0-1751322613207.png

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

View solution in original post

4 REPLIES 4
Ilgar_Zarbali
Most Valuable Professional
Most Valuable Professional

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.

MasonMA
Responsive Resident
Responsive Resident

@dirvine 

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. 

MasonMA_0-1751322613207.png

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!!!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.