Analistable

How to use Append Queries in Power Query

By the Analistable team · Updated · 5 min read

Load each table into Power Query, then choose Home → Append Queries → Append Queries as New. Pick Two tables or Three or more tables, add them in order, and click OK. Columns are matched by name; a column missing from one table is filled with null. Add a custom column before appending if you need to know which table each row came from.

Part of our guide: How to merge data in Power BI, Power Query and Tableau

Step by step in Excel

  1. Click in each source table and choose Data → From Table/Range. In the editor, choose Close & Load To → Only Create Connection.
  2. Choose Data → Get Data → Combine Queries → Append.
  3. Choose Three or more tables, move each table to Tables to append, and order them.
  4. Click OK, review the result in the editor, then Close & Load.

In Power BI: Transform data, select a query, then Home → Append Queries as New.

Append vs Append as New

Append Queries adds the other tables to the selected query, changing it. Append Queries as New creates a new query and leaves the sources unchanged — usually what you want, because the sources stay reusable.

How columns are matched

Appending Jan (Order, Amount) and Feb (Amount, Order, Discount)
OrderAmountDiscount
1001120null
100280null
1003955

Feb's columns are in a different order and it has an extra column — Append still lines everything up by name. Headers must match exactly, including case and spaces: “amount” and “Amount” become two columns. Rename first if needed.

Add a source column

In each source query, Add Column → Custom Column with a constant such as "Jan", or do it in M right at the append:

= Table.Combine({
    Table.AddColumn(Jan, "Month", each "Jan"),
    Table.AddColumn(Feb, "Month", each "Feb")
})

For a folder of files rather than tables in one workbook, use Combine Files from a folder. For joining tables side by side instead of stacking, see Merge Queries join kinds.

Common problems

Append problems and fixes
ProblemFix
Two similar columns (“Amount”, “amount”)Rename to one spelling before appending
Column types change to AnySet types after the append step
Duplicate rowsThe same rows exist in two sources; Remove Duplicates on the key after appending
New table not includedAppend lists tables by name; add it, or use Excel.CurrentWorkbook() to include all

For the last point, see merging every Excel table into one sheet.

Worked example: three regional tables

A workbook has three tables, North, South and West, each with Order, Rep and Amount. North has 120 rows totalling 48,200, South 150 rows totalling 61,950 and West 95 rows totalling 39,400. Append them as new with a Region column added to each:

let
    N = Table.AddColumn(North, "Region", each "North", type text),
    S = Table.AddColumn(South, "Region", each "South", type text),
    W = Table.AddColumn(West,  "Region", each "West",  type text),
    Combined = Table.Combine({N, S, W})
in
    Combined

The result should have 120 + 150 + 95 = 365 rows and a total Amount of 48,200 + 61,950 + 39,400 = 149,550. A PivotTable with Region in Rows confirms each source's contribution in one glance.

Appending every table automatically

Listing tables by hand means editing the query each time a table is added. In Excel, a blank query can pick up every table in the workbook instead:

let
    Source = Excel.CurrentWorkbook(),
    Tables = Table.SelectRows(Source, each Text.StartsWith([Name], "Sales_")),
    Combined = Table.Combine(Tables[Content])
in
    Combined

The filter on the name prefix matters: without it, the query also picks up its own output table once loaded to the sheet, and each refresh doubles the rows. Name your source tables consistently (Sales_North, Sales_South) so the filter stays simple.

Append in Excel versions and Power BI

Where the Append command lives
ProductPath
Excel for Microsoft 365, 2019, 2021 (Windows)Data → Get Data → Combine Queries → Append
Excel 2016 (Windows)Data → New Query → Combine Queries → Append
Power Query Editor (any)Home → Append Queries (drop-down: Append Queries as New)
Power BI DesktopTransform data → Home → Append Queries

Append, merge or a folder query?

  • Append: tables with the same columns that already live in your workbook or model (regional tabs, yearly tables).
  • Merge: tables that describe different things about the same key; it adds columns, not rows. See Merge Queries join kinds.
  • From Folder: separate files arriving every month. It appends them for you and picks up new files on refresh.
  • VSTACK (Microsoft 365): a formula alternative for small tables that should update instantly without a refresh, described in dynamic arrays for combining data.

Check the result after each refresh: the appended row count should equal the sum of the sources, and a column filter on your source column should show every table. A column you didn't expect, such as both “Amount” and “Amount ”, means a header has a trailing space in one source.

Types and errors after appending

Append keeps each column's type only when every source has the same type for it. If Amount is Decimal in North but Text in West (because someone typed “n/a”), the combined column becomes type Any and sums stop working. Set types once, as the last step after the append, and use Transform → Replace Errors or a filter on errors to find the rows that refuse to convert. Fixing the value at source is better than replacing it with null, because a null silently lowers the total.

Frequently asked questions

What does Append Queries do in Power Query?
It stacks the rows of two or more tables into one, matching columns by name.
Can I append tables with different columns?
Yes. Columns are matched by name, and columns missing from a table are filled with null.
How do I append more than two tables?
Choose Three or more tables in the Append dialog and add every table to the list.
Why does my appended query double every time I refresh?
An Excel.CurrentWorkbook() query is picking up its own output table. Filter the table list by name so the result table is excluded.
Is Append Queries the same as VSTACK?
They do the same job, stacking rows, but Append matches columns by name and runs on refresh, while VSTACK stacks ranges by position and recalculates instantly.
How do I know which table each appended row came from?
Add a custom column with a constant such as the table or region name to each source query before appending, or use Table.AddColumn inside Table.Combine.

Related guides