Power Query II: Combine

Why this matters

Real data does not arrive in one file. It arrives as this month's file, next to last month's file, next to a lookup list somebody maintains in a spreadsheet, next to a budget grid formatted for reading rather than analysis. This week's inbox contains all three shapes at once: six monthly sales exports (January through June 2026, one file each), the customer list you cleaned last lesson, and finance's budget crosstab — regions and categories down the side, twelve months marching across the columns.

Combining tables has two fundamentally different verbs, and confusing them is one of the oldest mistakes in data work. Append stacks — same-shaped tables, one on top of another, more rows. Merge looks up — different-shaped tables joined by a shared key, more columns. Six monthly files of identical shape? Append. Sales lines that need customer segments attached? Merge. Get them backwards and Power Query will not stop you; it will do exactly the wrong thing you asked for, fluently.

Append: the stack

Load all six monthly exports (each via Get data, or — the professional move once you have seen it — the Folder connector, which ingests every file in a directory in one step and is how this becomes twelve files next year without you touching anything). Each file has the same nine columns; January holds 3,903 rows, and the months grow from there.

Then: Home → Append Queries → Append Queries as New, select all six. One table: 26,139 rows, January through June, ready for a single line chart instead of six. A quick sanity check — build a temporary revenue total against it — gives $11,515,008.14, which reconciles with the company's H1 figure to the cent-level rounding difference you would expect. Verified, stacked, done.

Merge: the lookup

The appended sales table has a CustomerID column but no segments, no cities — those live in the customer table from Lesson 2. Home → Merge Queries: pick the sales table, pick the customer table, click the key column in each. Now the dialog asks the question this entire lesson exists to make you take seriously: Join Kind.

The experiment that settles it

Here is the situation, with real counts. The sales table has 26,139 rows. The marketing export you cleaned holds only 500 customers — a sample, not the full customer base. Merging on CustomerID:

Read the Inner row again. Choosing Inner instead of the default did not error, did not warn — it produced a smaller, plausible-looking table missing 94% of the company's sales. A revenue chart built on it would look completely professional and be catastrophically wrong. The join kind is not a technicality; it is a claim about which rows deserve to exist, and the row count afterward is how you audit the claim you made.

After a merge, the joined table arrives folded into a single column; click its expand icon to choose which of the customer columns to bring through (Segment and City, say — not all eleven). Every column you expand is a column every future refresh carries; take what the analysis needs.

If you know SQL — and if you don't

Students who have met SQL will recognize this whole section: Merge is JOIN, Append is UNION, and the join kinds are the same animals wearing friendlier names. If you have never touched SQL, you have just learned the concept that half of SQL interviews revolve around, with a dialog box instead of a keyword — the idea transfers whenever you get there.

Anti joins: the auditor's join

That Left Anti count — 24,512 sales with no matching customer — looked like a failure above. Flip the question and it becomes an instrument. "Which sales reference a customer missing from our marketing list?" is exactly the question an auditor, or a data-quality check, wants answered — and Left Anti answers it in one step. Keep this pattern: anti joins find orphans. Orders without valid products, employees without departments, payments without invoices. Any time two tables are supposed to agree, the anti join measures precisely how much they don't.

Unpivot: the crosstab comes in from the cold

Finance's budget_crosstab_2026.csv is formatted the way humans like to read: 30 rows — one per region-and-category pair — with twelve month columns marching across. Beautiful on paper. Useless for analysis: you cannot put "all twelve month columns" on a chart axis, filter to Q2, or join months against actuals, because the month is trapped in the column headers instead of living in the data.

The cure: select the two identity columns (Region, Category), then Transform → Unpivot Other Columns. The twelve month columns collapse into two: Attribute (the month, freed from the headers) and Value (the target). Rename them properly, set the types, and the grid becomes 360 tall rows — 30 × 12, verified — each one saying Region, Category, Month, Target. That shape joins, filters, and charts like every other table you own.

Common mistakes

• Merging when you meant appending (or the reverse). More rows wanted → append. More columns wanted → merge. Say the sentence before opening the dialog.
• Accepting the default join kind without deciding. Left Outer is a fine default precisely because it deletes nothing — but "I didn't choose" is not the same as "I chose Left Outer." The row count afterward is your signature on the decision.
• Merging on a key with duplicates on the right side. Each duplicate multiplies the matching left rows — the join that quietly grows your table. Profile the key column's distinct count first (500 rows, 500 distinct = safe).
• Expanding every column after a merge. Take Segment and City, not all eleven. Width is a cost every refresh pays.
• Unpivoting by selecting the month columns instead of "Unpivot Other Columns." Works today; breaks in January when a new month column arrives and isn't in the selection. Other-columns is change-proof in the direction data actually changes.

Summary