All lessons

Module 2: Power Query: get and clean data · Lesson 2.3 · about 16 min

Merge and append: joining tables

Append stacks tables with the same columns on top of each other; merge joins tables side by side on a key. Try all six join kinds and see which rows survive each one.

Module 2 · about 16 minutes · read first: 2.2. You’ll be able to choose between append and merge, pick the right join kind and spot the classic row-count mistakes.

1. Why this matters

Data rarely arrives in one table. Monthly files need stacking; order lines need product details. Getting joins wrong silently loses or doubles sales, and “explain inner vs left join” is one of the most common interview questions for any data job.

2. Append: stack rows

Append puts tables with the same columns under each other: January’s file, then February’s, then March’s.

From Date Store Amount
jan.csv 2025-01-03 Rideau 39.52
jan.csv 2025-01-04 Falls Centre 93.60
feb.csv 2025-02-01 Rideau 18.72

Columns are matched by name. A column missing from one file is filled with null, and “Amount” and “amount ” count as different columns, so clean names first. Even better: Get data > Folder combines every file in a folder and picks up new files on refresh.

3. Merge: join side by side

Merge adds columns from another table where a key matches, like VLOOKUP or SQL JOIN. Try each kind:

Try it: six kinds of merge

Order lines (left)

Products (right)

Merged result

Matched on ProductKey. Green rows found a partner; amber rows did not. Line 5 has ProductKey 13, which is not in the product list (a new product the list doesn't have yet), and the Snow shovel (12) was not sold on these lines.
Join kind Keeps Use it for
Left outer all left rows + matches Adding details to a fact table (the usual choice)
Inner only matching rows When unmatched rows really should go (rare in BI)
Right outer all right rows + matches The same as left, with the tables swapped
Full outer everything Reconciling two lists
Left anti left rows with no match Data quality: sales for unknown products
Right anti right rows with no match Products that never sold

In Power Query: Home > Merge queries, pick the second table, click the key column in each, choose the join kind, then expand (the ⇔ icon on the new column) to pick which columns to bring in.

4. Merge in Power Query, or a relationship in the model?

In Power BI you often don’t merge dimension details into the fact table at all: you load Product as its own table and relate it in the model (Module 3). Merge when you need to fix or enrich data before it reaches the model, such as mapping old product codes to new ones, or when a lookup table is tiny and only used once.

Principle

Principle: a join pairs every left row with every right row whose key matches. One match: one row. No match: kept or dropped depending on the kind. Two matches: two rows. So the row count after a left join equals the left count only if the right-hand key is unique.

Check: count rows before and after every merge. More rows means duplicate keys on the right; fewer means you used an inner join on data with gaps.

5. Worked example

Northbay’s new product list arrives with Desk lamp listed twice (two suppliers). A left merge of 7,328 order lines on ProductKey now gives about 7,950 rows: every desk lamp line appears twice, and Home sales jump by a third. The fix: remove duplicates on ProductKey in the product query (keeping the current supplier), then merge again and confirm 7,328 rows.

6. Practice

  1. Twelve monthly files, same columns: merge or append?
  2. Which join finds stores in the sales data that are missing from the store list?
  3. After merging, Sales went from 1,000 to 1,000 rows, but 40 rows have null Category. What does that tell you?
Answers
  1. Append (ideally with a Folder source).
  2. Left anti, with Sales on the left and the store list on the right (on StoreKey).
  3. 40 lines have a ProductKey that isn’t in the product table. Use a left anti join to list them and fix the product list.

Outside Power BI, merge CSV stacks or joins CSV files in your browser, which is handy for a quick check.

Check yourself

1You have one CSV per month with the same columns. Which do you use to make one table?
2Which join keeps every order line even when the product is missing from the product list?
3After a merge, the table has more rows than before. What is the most likely cause?

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.