How do I combine all tables in a workbook?

Use a blank Power Query with =Excel.CurrentWorkbook(), filter the table names you want and expand the Content column.

Why are my rows doubled after refresh?

The query is reading its own output table. Filter Excel.CurrentWorkbook() by a name prefix so the result table is excluded.

Does Excel.CurrentWorkbook include named ranges?

Yes, it returns tables and workbook-level named ranges. Filter by name to keep only what you need.

How to merge every Excel table into one sheet

By the Analistable team · Updated 2026-10-07 · 2 min read

To collect every table in a workbook onto one sheet without listing them, create a blank Power Query with =Excel.CurrentWorkbook(), filter out the result table itself, and expand the Content column. Tables you add later are picked up on refresh, and the Name column tells you which table each row came from.

Part of our guide: How to combine Excel sheets into one

When this technique helps

Some workbooks get a new table every month or for every client. Listing each one in VSTACK or in an Append step means editing the query every time. Excel.CurrentWorkbook() returns every table (and named range) in the current file, so one query covers all of them, now and later.

Step by step

Make sure each block of data is an Excel table (Ctrl+T) with the same column headers.
Choose Data → Get Data → From Other Sources → Blank Query.
In the formula bar, enter = Excel.CurrentWorkbook() and press Enter. You see one row per table, with Name and Content columns.
Filter the Name column to keep only the tables you want (for example, names that begin with “Sales_”).
Click the expand icon on Content, untick Use original column name as prefix, and click OK.
Rename the query (for example AllSales) and Close & Load.

The full query

let
    Source = Excel.CurrentWorkbook(),
    SalesTables = Table.SelectRows(Source, each Text.StartsWith([Name], "Sales_")),
    Combined = Table.ExpandTableColumn(
        SalesTables, "Content",
        Table.ColumnNames(SalesTables{0}[Content])
    )
in
    Combined

Filtering by a name prefix is important: when you load the result back into the workbook as a table, Excel.CurrentWorkbook() would otherwise include that table too and double every row on the next refresh.

If the tables' columns differ, replace the expand step with Table.Combine(SalesTables[Content]) to keep every column. See combining files with different columns.

Result

AllSales after refresh
Name	Order	Product	Amount
Sales_Jan	1001	Desk	420
Sales_Jan	1002	Lamp	65
Sales_Feb	1003	Chair	180
Sales_Mar	1004	Desk	420

Alternatives

A fixed set of tables: =VSTACK(Sales_Jan, Sales_Feb, Sales_Mar) — see combining Excel tables.
Tables in separate files: use From Folder — see combining Excel files into one spreadsheet.
No Power Query: the sheet consolidator stacks every sheet of a workbook in your browser.

Keep the tables consistent

Use one naming pattern for every table (Sales_Jan, Sales_Feb) so the filter catches new ones.
Keep the same headers in every table; rename columns before they're added, not after.
Set column types in the combined query (dates, decimals) rather than relying on each table's formatting.

Frequently asked questions

How do I combine all tables in a workbook?: Use a blank Power Query with =Excel.CurrentWorkbook(), filter the table names you want and expand the Content column.
Why are my rows doubled after refresh?: The query is reading its own output table. Filter Excel.CurrentWorkbook() by a name prefix so the result table is excluded.
Does Excel.CurrentWorkbook include named ranges?: Yes, it returns tables and workbook-level named ranges. Filter by name to keep only what you need.