Control the appended schema
Produce the same set of columns from an append however the inputs vary.
~8 min read · intermediate · Module 11: Appending tables
By Tahir Nas · Examples run in our simulator · Updated
Before this: Append tables and align their columns, When a column goes missing: MissingField options
By default an append returns the union of every input's columns. That's convenient until an input gains a column nobody wants (an internal Notes field, a system timestamp), and it appears in the report. When the output layout is fixed, say so in the query.
Table.Combine with a column list
Table.Combine takes an optional second argument: the columns to return. Columns not listed are dropped, and listed columns missing from an input are filled with null for its rows:
let
Store = #table({"Ref", "Amount", "Till"}, {{"S-1", 20, "T3"}}),
Web = #table({"Ref", "Amount", "Coupon"}, {{"W-1", 12, "AUTUMN"}}),
Combined = Table.Combine({Store, Web}, {"Ref", "Amount", "Coupon"})
in
CombinedResult
| 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.
Align each input first
The same result can be built input by input, with Table.SelectColumns and MissingField.UseNull. It's more verbose, but each input's alignment is visible as its own step, which helps when inputs also need renaming:
let
Columns = {"Ref", "Amount", "Coupon"},
Store = #table({"Ref", "Amount", "Till"}, {{"S-1", 20, "T3"}}),
Web = #table({"Ref", "Amount", "Coupon"}, {{"W-1", 12, "AUTUMN"}}),
Aligned = List.Transform({Store, Web}, each Table.SelectColumns(_, Columns, MissingField.UseNull)),
Combined = Table.Combine(Aligned)
in
CombinedResult
| 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.
Which approach?
| Situation | Use |
|---|---|
| Inputs share names; you just want a fixed set of columns | Column list in Table.Combine |
| Inputs need renaming or per-input fixes | Align each input, then combine |
| New input columns should flow through | Plain Table.Combine (the default union) |
Keep the column list in one named step (like Columns above) so that changing the output layout is a single edit, and so readers can see the contract at a glance.
Where to find this in Excel
- Excel for Windows
- Home > Append Queries, then Home > Choose Columns
- What you see
- Choose Columns fixes the list of columns the combined table keeps, whatever the sources add later.
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); Choose or remove columns (Microsoft Learn).
Common mistakes
- Letting a stray input column reach a fixed-layout report through the default union.
- Assuming a fixed column list protects against renamed source columns; it turns them into nulls.
- Maintaining the column list in several places instead of one named step.
Excel, Power BI and the simulator
- The Append dialog in Excel and Power BI Desktop always writes the plain union; add the column list in the formula bar.
- Table.Combine's second argument can also be a table type, which sets column types at the same time; the simulator supports the list form shown here.
- The simulator fills listed-but-missing columns with null, as in the native function.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Appending tables quiz