How to load multiple excel workbooks each with multiple worksheets?

[dirvine]

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"

[Ilgar_Zarbali]

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.

[dirvine]

This didn't work, I am still only geting results from the first tab in the workbook.

[MasonMA · Accepted solution]

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

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

[dirvine]

The video was key! Thank you!!!