Power Query Merge: Join Kinds and Pitfalls

Merge Queries in Power Query joins two tables side by side by matching key columns, the same job SQL's JOIN does; Append Queries instead stacks two tables' rows on top of each other, the same job SQL's UNION ALL does. Picking the wrong one is the most common Power Query mistake, closely followed by a duplicate key on one side quietly multiplying rows after a merge.

What's the difference between Merge and Append in Power Query?

Merge adds columns (a horizontal join on a matching key), and Append adds rows (a vertical stack of tables with matching column structure) — the same distinction as SQL JOIN vs. UNION ALL, or Excel VLOOKUP vs. copy-pasting one table under another.

Merge (join):                    Append (union):
Orders + Customers               Jan Sales + Feb Sales + Mar Sales
→ adds Customer columns          → stacks all rows into one table
  to each Order row               (same columns in each source)

The SQL equivalents are covered in inner join in SQL and UNION vs. UNION ALL if you're translating between the two mental models.

How do I merge two queries?

Open the query you want to add columns to, choose Merge Queries, pick the second table and the matching key column(s) in both, choose a join kind, and click OK — the result is a new column containing a nested table, which you then expand into real columns.

1. Home tab > Merge Queries (or Merge Queries as New to keep the original untouched)
2. Select the second table (e.g. Customers)
3. Click the key column in both tables (e.g. CustomerID in each)
4. Join Kind: Left Outer (default)
5. Click OK → a new column appears, containing a "Table" value per row

What are the join kinds and when do I use each?

Power Query offers six join kinds, and the choice determines which unmatched rows survive the merge — the two you'll use almost every time are Left Outer (keep everything from the first table) and Inner (keep only rows that match in both).

Join kind	Keeps	SQL equivalent
Left Outer (default)	All rows from the first table, matched columns where found	LEFT JOIN
Right Outer	All rows from the second table	RIGHT JOIN
Full Outer	All rows from both tables	FULL OUTER JOIN
Inner	Only rows with a match in both tables	INNER JOIN
Left Anti	Only first-table rows with NO match in the second	LEFT JOIN ... WHERE right.key IS NULL
Right Anti	Only second-table rows with NO match in the first	RIGHT JOIN ... WHERE left.key IS NULL

How do I expand the merged column into real columns?

Click the two-arrow expand icon in the new column's header, tick the specific columns you want (unticking the key column to avoid a duplicate), and decide whether to keep the "Use original column name as prefix" option — turning it off is usually cleaner unless both tables have overlapping column names that would otherwise collide.

Click the ⇄ icon on the new merged column's header
→ Select columns: CustomerName, Region (uncheck CustomerID, already have it)
→ Uncheck "Use original column name as prefix" if names won't collide
→ OK

If you instead need one aggregated value per matched group (like counting how many orders each customer has, rather than pulling in every column), click the ⇅ "Aggregate" option in the same dialog instead of expanding individual columns.

What common mistakes break a Power Query merge?

Most merge problems come down to the key columns not actually matching character-for-character, or a duplicate key on one side fanning rows out further than expected.

• Case sensitivity: Power Query's default text comparison is case-sensitive, so "abc123" and "ABC123" do not match — unlike VLOOKUP or XLOOKUP, which are case-insensitive. Normalize case first with Text.Upper or Text.Lower on both key columns if the source data is inconsistent.
• Mismatched data types: a key stored as text in one table and as a number in the other never matches; check both columns' type icons in the column header before merging.
• Trailing spaces or hidden characters: " 1001" ≠ "1001"; apply Text.Trim to both key columns if the source is messy.
• Duplicate keys on the "many" side: unlike VLOOKUP/XLOOKUP, which silently return only the first match, a merge returns every matching row — a duplicate key on the second table multiplies rows in the result (a fan-out), inflating any downstream sum.
• Merging on the wrong join kind by default: Left Outer is Power Query's default even when you actually wanted Inner (drop unmatched rows) — always confirm the join kind explicitly rather than accepting the default unread.

How do I dedupe before or after a merge?

A duplicate key on the "many" side of a merge is the single biggest cause of row-count surprises after a merge — check for and remove duplicates on the key column before merging if you expect a one-to-one match. See removing duplicates in Power Query for the dedupe step and how it differs from the Remove Duplicates button on the Data ribbon.