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
CombinedResult
| OrderID | Amount | Channel |
|---|---|---|
| S-1 | 20 | Store |
| S-2 | 35 | Store |
| W-1 | 12 | Web |
| P-1 | 50 | Phone |
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
| Expected | Actual | Balanced |
|---|---|---|
| 4 | 4 | true |
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.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Appending tables quiz