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
- Click in each source table and choose Data → From Table/Range. In the editor, choose Close & Load To → Only Create Connection.
- Choose Data → Get Data → Combine Queries → Append.
- Choose Three or more tables, move each table to Tables to append, and order them.
- 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
| Order | Amount | Discount |
|---|---|---|
| 1001 | 120 | null |
| 1002 | 80 | null |
| 1003 | 95 | 5 |
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
| Problem | Fix |
|---|---|
| Two similar columns (“Amount”, “amount”) | Rename to one spelling before appending |
| Column types change to Any | Set types after the append step |
| Duplicate rows | The same rows exist in two sources; Remove Duplicates on the key after appending |
| New table not included | Append 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
CombinedThe 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
CombinedThe 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
| Product | Path |
|---|---|
| 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 Desktop | Transform 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.