Append tables and align their columns
Stack tables correctly, including ones whose column names differ.
~9 min read · beginner · Module 11: Appending tables
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Rename and reorder columns
Appending stacks rows: January below December, web sales below store sales. Table.Combine matches columns by name, not by position, so the order of columns in each table doesn't matter, but their names do.
let
Jan = #table({"OrderID", "Amount"}, {{"J-1", 20}, {"J-2", 35}}),
Feb = #table({"Amount", "OrderID"}, {{12, "F-1"}}),
Appended = Table.Combine({Jan, Feb})
in
AppendedResult
| OrderID | Amount |
|---|---|
| J-1 | 20 |
| J-2 | 35 |
| F-1 | 12 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
When names differ
If one table says Amount and the other Total, appending gives two half-empty columns. Rename first so the names line up:
let
Store = #table({"Ref", "Amount"}, {{"S-1", 20}}),
Web = #table({"OrderRef", "Total"}, {{"W-1", 12}}),
Misaligned = Table.Combine({Store, Web}),
Aligned = Table.Combine({Store, Table.RenameColumns(Web, {{"OrderRef", "Ref"}, {"Total", "Amount"}})})
in
#table({"Version", "Columns", "Rows"}, {
{"Without renaming", Text.Combine(Table.ColumnNames(Misaligned), ", "), Table.RowCount(Misaligned)},
{"After renaming", Text.Combine(Table.ColumnNames(Aligned), ", "), Table.RowCount(Aligned)}
})Result
| Version | Columns | Rows |
|---|---|---|
| Without renaming | Ref, Amount, OrderRef, Total | 2 |
| After renaming | Ref, Amount | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Columns that exist in only one table
The result contains every column from every input. Rows from a table without that column get null. That's usually correct (store sales have no coupon code), so don't delete such columns just to make schemas match:
Table.Combine({
#table({"Ref", "Amount"}, {{"S-1", 20}}),
#table({"Ref", "Amount", "Coupon"}, {{"W-1", 12, "AUTUMN"}})
})Result
| Ref | Amount | Coupon |
|---|---|---|
| S-1 | 20 | null |
| W-1 | 12 | AUTUMN |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The appended table keeps the rows in input order: all of the first table, then all of the second. If the order matters later (a running balance, a line number), sort explicitly after appending rather than relying on that.
Before appending, compare the column names of the inputs side by side: Table.ColumnNames(Jan) and Table.ColumnNames(Feb). A difference in spelling, spacing or case is far easier to fix before the append than after it.
Where to find this in Excel
- Excel for Windows
- Home > Append Queries (Combine group)
- What you see
- Choose Two tables or Three or more tables. Columns are matched by name, and a column missing from one table fills with null.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Append queries (Microsoft Learn).
Common mistakes
- Appending tables whose column names differ slightly (Amount vs amount), producing half-empty duplicate columns.
- Expecting columns to match by position, as in a copy-paste.
- Using a merge when the second table holds more rows of the same thing.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Home → Append Queries (adds a step) or Append Queries as New (creates a new query). Choose Three or more tables to append a list.
- Both hosts match columns by name, case-sensitively.
- The simulator's Table.Combine behaves the same, including null for missing columns.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Stack two months of orders (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~6 minPro
Stack two months of orders (kite shop)
Append February's orders below January's to get one table.
- beginner~6 minPro
Stack two months of orders (bakery)
Append February's orders below January's to get one table.
- beginner~6 minPro
Stack two months of orders (bike rental)
Append February's orders below January's to get one table.
- intermediate~9 minPro
Append store and web sales with different column names (bookshop)
Align WebSales column names to StoreSales before appending, and see where nulls come from.
- intermediate~9 minPro
Append store and web sales with different column names (garden centre)
Align WebSales column names to StoreSales before appending, and see where nulls come from.
- beginner~7 minPro
Append three store tables and label each row's store (kite shop)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- beginner~7 minPro
Append three store tables and label each row's store (bakery)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- beginner~7 minPro
Append three store tables and label each row's store (bike rental)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- intermediate~8 minPro
Append tables whose columns differ in order and set (kite shop)
Web orders have a Discount column, phone orders a Channel column, and their columns are in different orders. Append by name.
- intermediate~8 minPro
Append tables whose columns differ in order and set (bakery)
Web orders have a Discount column, phone orders a Channel column, and their columns are in different orders. Append by name.
- intermediate~7 minPro
Append two exports that overlap (kite shop)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
- intermediate~7 minPro
Append two exports that overlap (bakery)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
- intermediate~7 minPro
Append two exports that overlap (bike rental)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
- intermediate~8 minPro
Align column types before appending (bookshop)
Shop sales come from a CSV (Amount is text); online sales come from a table (Amount is a number). Make Amount numeric for both.
- intermediate~8 minPro
Align column types before appending (nursery)
Shop sales come from a CSV (Amount is text); online sales come from a table (Amount is a number). Make Amount numeric for both.
- advanced~11 minPro
Append every monthly table in a workbook (kite shop)
A workbook has one Excel table per month (Sales_Jan, Sales_Feb…) plus a notes sheet and a targets table. Append just the monthly sales tables.
- advanced~11 minPro
Append every monthly table in a workbook (bakery)
A workbook has one Excel table per month (Sales_Jan, Sales_Feb…) plus a notes sheet and a targets table. Append just the monthly sales tables.