Merging and Appending Queries
February brings two jobs. Pune and Mumbai send separate files that must become one Sales table, and each sale needs its product's name and category from the price list. One job adds rows, the other adds columns. Power Query calls them Append and Merge, and confusing the two is a classic beginner mistake.
The rule
Append stacks rows underneath; merge matches rows and adds columns beside them.
Append (Home → Append Queries) stacks tables that describe the same kind of thing. Columns are matched by name. If a column exists in only one table, the rows from the other table get null in it. Append Queries as New builds a new query and leaves the originals alone.
Merge (Home → Merge Queries) is a join. You pick two tables, the key column in each, and a join kind. The result is a new column holding a small nested table per row; click its expand icon to choose which columns to bring across.
Take February's Mumbai lines, whose ProductIDs are P3, P1, P2 and P7 (P7 is a new cookie the price list does not have yet), and a Products table with P1, P2, P3 and P4 (P4, ginger biscuits, has never sold). With Sales first and Products second:
| Join kind | Keeps | Rows here |
|---|---|---|
| Left outer | Every Sales row, plus matches from Products | 4 — P7 gets null product details |
| Right outer | Every Products row, plus matches from Sales | 4 — P4 gets null sales details |
| Full outer | Every row from both | 5 — three matches, plus P7, plus P4 |
| Inner | Only rows that match | 3 |
| Left anti | Sales rows with no match | 1 — P7 |
| Right anti | Products rows with no match | 1 — P4 |
A merge multiplies rows when the lookup key repeats. If Products listed P1 twice, every P1 sale would appear twice after the merge, and revenue would double. Check that the key is unique on the lookup side first.
The shortcut
Ask "More rows, or more columns?" More rows of the same kind → Append. More columns about the same rows → Merge. For a merge, ask one more question: "Whose rows must all survive?" Usually it is the fact table, which means Left outer with the fact first.
More rows: append
Pune has 3 lines and Mumbai has 4, with the same columns. Append gives one Sales table of 7 rows.
Drills
Answer fast — trust the rule.
Pune's file has 3 rows and Mumbai's has 4, with the same columns. How many rows does appending them give?
Sales (P3, P1, P2, P7) is merged with Products (P1 to P4) using an Inner join. How many rows result?
With Sales first and Products second, which join kind lists the products that have never sold?
With a Left outer merge, Sales first, what happens to the P7 row?
Mumbai's file calls the column Quantity, while Pune's calls it Qty. What does Append produce?
Remember
- Append stacks rows underneath; merge matches rows and adds columns beside them.
- Ask: more rows or more columns — and whose rows must all survive?
- Traps: column names that differ before an append, and a repeated key that multiplies rows in a merge.