What is the default join kind in Power Query?

Left outer: all rows from the first table and matching rows from the second.

How do I find rows that don't match in Power Query?

Use a Left anti join (rows in the first table with no match) or a Right anti join (rows in the second table with no match).

Is Power Query merge case-sensitive?

Yes for exact merges. Clean both key columns to the same case, or use fuzzy matching with Ignore case.

Power Query Merge Queries: every join kind explained

By the Analistable team · Updated 2026-10-07 · 2 min read

Power Query's Merge dialog offers six join kinds. Left outer keeps every row of the first table (the default). Right outer keeps every row of the second. Full outer keeps everything. Inner keeps only matches. Left anti returns first-table rows with no match, and Right anti second-table rows with no match — ideal for finding missing records.

Part of our guide: How to merge data in Power BI, Power Query and Tableau

The example

Orders has customer IDs C-101, C-103, C-101 and C-104. Customers has C-101, C-102 and C-103. Merging Orders (first) with Customers (second) on Customer ID:

Rows returned by each join kind
Join kind	Rows	Contains
Left outer	4	All 4 orders; C-104's customer columns are null
Right outer	4	3 matched orders + Hart & Co (C-102) with null order columns
Full outer	5	All orders and all customers
Inner	3	Only orders whose customer exists
Left anti	1	O-9004 (customer C-104 not found)
Right anti	1	Hart & Co (no orders)

When to use each

Left outer: add lookup columns to a main table without losing rows (the most common).
Inner: analyse only complete records.
Full outer: reconcile two lists and see everything that's in either.
Left anti / Right anti: data quality and reconciliation — orders with unknown customers, customers without orders, items in last month's list but not this month's.
Right outer: rarely needed; swap the table order and use left outer instead, which is easier to read.

Doing the merge

Load both tables as queries.
Home → Merge Queries (or Data → Get Data → Combine Queries → Merge in Excel).
Select the matching column in each table. Ctrl+click to match on several columns — the order you click them pairs them up.
Choose the join kind and click OK.
Expand the new column. For anti joins, there's nothing to expand: remove the column and keep the rows.

The M function

= Table.NestedJoin(Orders, {"Customer ID"}, Customers, {"Customer ID"}, "Customers", JoinKind.LeftAnti)

The JoinKind values are LeftOuter, RightOuter, FullOuter, Inner, LeftAnti and RightAnti. Power Query's key matching is case-sensitive: “c-101” won't match “C-101”. Clean keys with Text.Upper and Text.Trim first.

For names that are spelled slightly differently, use fuzzy merge. For the same concepts outside Power Query, see joining spreadsheets.

Check the match count

The Merge dialog shows how many rows of the first table found a match (for example “The selection matches 3 of 4 rows from the first table”). Read it before clicking OK. A much lower number than expected nearly always means the keys need cleaning.

Frequently asked questions

What is the default join kind in Power Query?: Left outer: all rows from the first table and matching rows from the second.
How do I find rows that don't match in Power Query?: Use a Left anti join (rows in the first table with no match) or a Right anti join (rows in the second table with no match).
Is Power Query merge case-sensitive?: Yes for exact merges. Clean both key columns to the same case, or use fuzzy matching with Ignore case.