Power QueryExercises

Append many tables and tag where each row came from

Combine a list of tables while keeping each row traceable to its source.

~9 min read · intermediate · Module 11: Appending tables

By Tahir Nas · Examples run in our simulator · Updated

Before this: Append tables and align their columns

Once tables are stacked, you can't tell a store sale from a web sale unless a column says so. Tag each table before appending, while you still know which is which.

Tag, then combine

Keep the channel name next to each table in a list of pairs, add the tag column to every table with List.Transform, and combine the result:

let
    Store = #table({"OrderID", "Amount"}, {{"S-1", 20}, {"S-2", 35}}),
    Web = #table({"OrderID", "Amount"}, {{"W-1", 12}}),
    Phone = #table({"OrderID", "Amount"}, {{"P-1", 50}}),
    Channels = {{"Store", Store}, {"Web", Web}, {"Phone", Phone}},
    Tagged = List.Transform(Channels, (pair) => Table.AddColumn(pair{1}, "Channel", each pair{0}, type text)),
    Combined = Table.Combine(Tagged)
in
    Combined

Result

OrderIDAmountChannel
S-120Store
S-235Store
W-112Web
P-150Phone

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

pair{0} is the name and pair{1} the table. The inner each can still see pair from the outer function.

Adding the channel by hand to each query works too, but a list makes adding a fourth channel a one-line change, and guarantees the tag is spelled the same way everywhere.

Check the row count

An append never adds or removes rows: the result should have exactly the sum of its inputs' rows. That's a cheap check worth keeping in critical queries:

let
    Inputs = {
        #table({"OrderID"}, {{"S-1"}, {"S-2"}}),
        #table({"OrderID"}, {{"W-1"}}),
        #table({"OrderID"}, {{"P-1"}})
    },
    Expected = List.Sum(List.Transform(Inputs, Table.RowCount)),
    Actual = Table.RowCount(Table.Combine(Inputs))
in
    #table({"Expected", "Actual", "Balanced"}, {{Expected, Actual, Expected = Actual}})

Result

ExpectedActualBalanced
44true

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

If a later step filters or deduplicates the combined table, compare counts before that step; the append itself should always balance.

Tagging folders of files

When the tables come from files in a folder, the file name is the natural tag. Combine files from a folder keeps the Name column for exactly this reason.

Where to find this in Excel

Excel for Windows
Home > Append Queries > Append Queries as New
What you see
The tables stay unchanged and a new query holds the combined rows; a source tag is a custom column you add before appending.

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); Add a custom column (Microsoft Learn).

Common mistakes

  • Appending first and trying to work out the source afterwards.
  • Spelling the tag differently in different queries (Web, web, Website).
  • Not checking that the combined row count equals the sum of the inputs.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Append Queries → Three or more tables lets you pick the list; the tag column is added in each source query or with a custom step like the one here.
  • In Power BI, keeping each channel as its own staging query with Enable load off, then appending, is a common tidy pattern.
  • The simulator supports List.Transform with nested functions and Table.Combine over a list of any length.
Functions in this lesson:Table.CombineTable.AddColumnList.TransformList.Sum

Next step

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

Take the Appending tables quiz