Power QueryExercises

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
    Combined

Result

RefAmountCoupon
S-120null
W-112AUTUMN

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Till is dropped; Coupon is kept and is null for the store row.

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
    Combined

Result

RefAmountCoupon
S-120null
W-112AUTUMN

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Which approach?

SituationUse
Inputs share names; you just want a fixed set of columnsColumn list in Table.Combine
Inputs need renaming or per-input fixesAlign each input, then combine
New input columns should flow throughPlain 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.
Functions in this lesson:Table.CombineTable.SelectColumns

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Appending tables quiz