How to Compare Two Tables in Power Query (Anti Join Explained)
Power Query's Merge Queries dialog can answer every version of "what's different between these two tables" without writing a single formula, once you know which Join Kind to pick. The anti join options are the ones most people are actually looking for and the least self-explanatory by name.
Quick answer
Home > Merge Queries, select both tables, click the matching key column in each, then set Join Kind to Left Anti to get rows only in the first table, or Right Anti for rows only in the second. Use Inner to get rows that exist in both. Power Query is case-sensitive by default, so normalize casing with Text.Lower() on the key columns first if that matters.
Left Anti: what's only in Table A
Go to Home > Merge Queries (or Merge Queries as New to keep both originals untouched). Select Table A as the first table and Table B as the second. Click the key column in Table A, then click the matching key column in Table B - both should highlight. Set Join Kind to Left Anti, then click OK. The result is every row from Table A whose key does not exist anywhere in Table B.
Right Anti: what's only in Table B
Same dialog, same key columns selected, but set Join Kind to Right Anti. This flips the direction: you get rows from Table B whose key has no match in Table A. If you need both directions, run the merge twice with different join kinds rather than trying to get both from a single query.
Inner: what's in both
Set Join Kind to Inner instead. This keeps only rows whose key exists in both tables - the intersection. Combined with expanding the merged column, you can also see the matched values side by side to spot rows where the key matches but another column's value differs.
List.Difference in M code
let
a = {"apple", "banana", "cherry"},
b = {"banana", "date"},
aOnly = List.Difference(a, b)
in
aOnly
// {"apple", "cherry"} If you're already in the Advanced Editor working with a plain List value rather than a full table, List.Difference(list1, list2) is the direct M-code equivalent of a Left Anti merge - no dialog needed. It's the function running behind the scenes for the simplest possible version of this problem.
Case sensitivity and other match pitfalls
Power Query compares key columns as case-sensitive text by default, which trips up people coming from Excel formulas like VLOOKUP or COUNTIF that ignore case. Normalize both sides with Text.Lower() in a custom column before merging if that matters. The other common cause of unexpected zero matches is a data type mismatch - one key column typed as Text and the other as Whole Number never match even when they display identically, so check the type icon in each column header before merging.
No Power Query needed for a simple list comparison. Paste both lists.
Open Compare 2 Lists →Comparing plain spreadsheet columns instead of Power Query tables?
See the guide to comparing two Excel columns without formulas.