Power QueryExercises

Merge vs append in Power Query: which one do you need?

Comparison · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Append stacks the rows of a January table and a February table with the same columns into one three-row table. Merge matches each store to its manager in a second table and adds a Manager column to the sales table.
Append adds rows of the same kind; merge adds columns from matching rows.

The one-question test

Ask: does the second table contain more rows of the same thing, or more facts about the rows I already have? More rows of the same thing (another month, another store, another system's orders) is an append. More facts about existing rows (the customer's city, the product's price) is a merge.

AppendMerge
What it addsRowsColumns
How tables line upBy column nameBy key values
FunctionTable.CombineTable.NestedJoin + expand
Rows afterwardsSum of the inputsDepends on join kind and key uniqueness
Excel analogyPasting one range under anotherVLOOKUP / XLOOKUP
Typical mistakeMisnamed columns create half-empty duplicatesDuplicate keys multiply rows

Append in one example

let
    August = #table({"OrderID", "Customer", "Amount"}, {{"A-1", "C1", 20}, {"A-2", "C2", 35}}),
    September = #table({"OrderID", "Customer", "Amount"}, {{"S-1", "C1", 12}}),
    AllOrders = Table.Combine({August, September})
in
    AllOrders

Result

OrderIDCustomerAmount
A-1C120
A-2C235
S-1C112

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

2 + 1 = 3 rows. An append never changes the number of rows beyond the sum.

Merge in one example

let
    Orders = #table({"OrderID", "Customer", "Amount"}, {{"A-1", "C1", 20}, {"A-2", "C2", 35}, {"S-1", "C1", 12}}),
    Customers = #table({"Customer", "City"}, {{"C1", "Leeds"}, {"C2", "York"}}),
    Merged = Table.NestedJoin(Orders, {"Customer"}, Customers, {"Customer"}, "Cust", JoinKind.LeftOuter),
    WithCity = Table.ExpandTableColumn(Merged, "Cust", {"City"})
in
    WithCity

Result

OrderIDCustomerAmountCity
A-1C120Leeds
A-2C235York
S-1C112Leeds

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

Same three rows, one more column. With a unique customer key, a left outer merge keeps the row count.

Often you need both

A monthly report typically appends the months first (one long orders table), then merges reference data onto it once. Doing it the other way round (merging each month separately, then appending) works but repeats the merge and its checks for every month.

What goes wrong

  • Appending tables that are really lookups. Stacking a customer list under an orders list produces a table where half the columns are null on every row.
  • Merging when you meant to append. Joining September onto August by OrderID finds no matches, because the orders are different orders.
  • Column names that almost match. Amount and amount are different columns to an append; rename first.
  • Duplicate keys in a merge. If C1 appears twice in Customers, every C1 order appears twice. Check the key with Table.IsDistinct first.

Practise both with the tasks below: stacking two months of orders, then looking up each order's customer city, then finding the orders whose customer is missing (a left anti merge).

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.