Combine All Sheets in One Excel File in Power Query M

Reads every worksheet in a single Excel workbook and stacks them into one combined table.

File HandlingIntermediateexcelsheetscombineworkbook

Power Query M code

let
    Source = Excel.Workbook(File.Contents("C:\Data\Workbook.xlsx"), true),
    OnlySheets = Table.SelectRows(Source, each [Kind] = "Sheet"),
    CombinedData = Table.Combine(List.Transform(OnlySheets[Data], each Table.PromoteHeaders(_, [PromoteAllScalars = true])))
in
    CombinedData

Notes

Table.Combine unions columns across sheets even if their layouts differ slightly; filter OnlySheets further if you only want specific sheets.

Requirements

A single Excel workbook with multiple sheets sharing a similar layout.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Combine All Sheets in One Excel File.