Analistable

How to merge every Excel table into one sheet

By the Analistable team · Updated · 5 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

  1. Make sure each block of data is an Excel table (Ctrl+T) with the same column headers.
  2. Choose Data → Get Data → From Other Sources → Blank Query.
  3. In the formula bar, enter = Excel.CurrentWorkbook() and press Enter. You see one row per table, with Name and Content columns.
  4. Filter the Name column to keep only the tables you want (for example, names that begin with “Sales_”).
  5. Click the expand icon on Content, untick Use original column name as prefix, and click OK.
  6. 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
NameOrderProductAmount
Sales_Jan1001Desk420
Sales_Jan1002Lamp65
Sales_Feb1003Chair180
Sales_Mar1004Desk420

Alternatives

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.

Add the table name as a column

Excel.CurrentWorkbook() returns a Name column alongside each table's Content. Keep it when you expand Content and every row carries the name of the table it came from — useful when the tables are named Sales_Jan, Sales_Feb and so on. You can then split the name into a month with Transform → Split Column → By Delimiter, using the underscore.

Only some tables

If the workbook also contains lookup tables you don't want to stack, filter the Name column before expanding. A naming convention makes this simple: start every table to combine with the same prefix and filter with Text Filters → Begins With. In M the step looks like this:

Filtered = Table.SelectRows(Source, each Text.StartsWith([Name], "Sales_"))

The filter also stops the query from picking up its own output table, which is the usual cause of rows doubling on refresh.

Worked example: four tables, one sheet

A workbook holds Sales_Jan (120 rows), Sales_Feb (98 rows), Sales_Mar (134 rows) and a Rates table. Filtering to names beginning with Sales_ and expanding gives 120 + 98 + 134 = 352 rows. After the first refresh, the output table, called something like Query1 or the query's name, also appears in Excel.CurrentWorkbook(); because it does not start with Sales_, the filter keeps it out and the result stays at 352 rows.

Tables with different columns

When you click the expand button, Power Query lists the columns of the tables it sampled. If a later table has an extra column, it may be left out of the list. To make sure every column is included, replace the fixed column list in the expand step with one built from all the tables:

Columns = List.Union(List.Transform(Filtered[Content], Table.ColumnNames)),
Expanded = Table.ExpandTableColumn(Filtered, "Content", Columns)

Missing values become null. Clean them with Transform → Replace Values if you need zeros instead.

Troubleshooting

When the merged sheet looks wrong
SymptomCauseFix
Rows double on every refreshThe query reads its own output tableFilter the Name column to the source tables
A new table isn't includedIts name doesn't match the filterRename it to the agreed prefix
A column is missingExpand used a fixed column listBuild the list with List.Union
Numbers arrive as textData type set to Any after the expandSet types in a step after the expand
Excel.CurrentWorkbook not availableRanges are not formatted as tablesPress Ctrl+T on each range and name it

For tables spread over several files rather than one workbook, see combining Excel files into one spreadsheet.

Common mistakes

  • Loading the result to a table whose name matches the filter prefix, for example naming the output Sales_All. It is then read back in on the next refresh.
  • Setting column types before the expand step; types have to be applied to the expanded columns.
  • Converting a range to a table but leaving the default name Table3, so it can't be recognised by prefix.
  • Editing the output table by hand. Typed changes are lost the next time the query refreshes, so make corrections in the source tables.

To repeat the process monthly, add the new month as another table with the agreed prefix and press Data → Refresh All; nothing in the query needs editing.

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.

Related guides