Guide Power Query
I need a proper join, not fifty thousand lookups
A column of XLOOKUP works until the workbook takes ten seconds to recalculate. Power Query does the same job as a real join, computed once when you refresh, and it can answer questions a lookup cannot.
Load both tables as queries, then Home → Merge Queries, pick the matching column in each, and choose a join kind. Power Query offers all six by name: inner, left outer, right outer, full outer, left anti and right anti. The anti joins are the ones no lookup formula can do — they return the rows that did not match, which is exactly what a reconciliation needs.
What a lookup cannot do
A lookup answers one question: for this key, give me the matching value. That covers a lot, and it misses three things people regularly need.
- Rows that did not match. A lookup gives you
#N/Aand leaves you to filter it. An anti join returns exactly those rows as a table. - Every matching row, not the first. A lookup returns one value. A merge can return all the matches.
- Speed at size. Fifty thousand lookup formulas are recalculated on every change. A merge is computed once, on refresh.
The vocabulary is the same as SQL, which is deliberate: if you have ever used a database, the dialog will read like a familiar sentence.
| What you see | What actually happened | What fixes it |
|---|---|---|
| Workbook recalculates for seconds | Thousands of lookups over whole columns | Replace them with one merge |
| Need the rows that did NOT match | A lookup can only give you #N/A | Use a left anti join |
| Need every match, not the first | A lookup returns a single value | Merge, then expand |
| More rows after merging than before | Duplicate keys on the other side — correct behaviour | Deduplicate the lookup table first |
| The merge matches nothing | Key types differ, or invisible characters | Set both keys to Text and trim them |
How to merge two tables in Power Query
Load both tables as queries
Click in the first table, Data → From Table/Range, then Close & Load To → Only Create Connection. Repeat for the second. Both now exist as queries without writing anything to a sheet.
Merge them on the key column
Open the first query and choose Home → Merge Queries. Pick the second query underneath. Click the key column heading in the top table and the matching one in the bottom — both highlight when they are selected.
The dialog tells you how many rows will match, at the bottom. Read it. If it says 0 of 50,000, stop and fix the keys before going further.
Choose the right join kind
The six options, in plain terms:
- Left outer — every row from the first table, plus matches where they exist. This is what a lookup does, and it is the default.
- Inner — only rows that matched on both sides.
- Right outer — every row from the second table instead.
- Full outer — everything from both, matched where possible.
- Left anti — only rows from the first table with no match. The reconciliation answer.
- Right anti — only unmatched rows from the second table.
Expand only the columns you need
The merge adds one column holding the whole matched table. Click the double-arrow in its heading and tick only the columns you actually want — expanding all thirty when you need two makes the query slow for no reason.
Untick Use original column name as prefix unless you like headings such as
Customers.Customers.Name.
Check the row count afterwards
A left outer join should return the same number of rows you started with. More means the second table has duplicate keys, and each duplicate multiplied its row.
That is correct join behaviour, not a bug — but it is almost never what you wanted. Remove duplicates from the lookup table first.
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
The anti join is the one to learn
Almost nobody knows it exists, and it answers the question a reconciliation is actually made of: what is in my list that is not in theirs?
Two anti joins, run in both directions, give you a complete reconciliation in about a minute: the invoices with no payment, and the payments with no invoice. Formulas can do it, but they cannot hand you the two lists as tables.
Clean the keys before you merge
Merging matches values exactly, so everything from the basics applies here too: a trailing space, a non-breaking space, or one side stored as text and the other as a number will all produce a merge that matches nothing. Trim and set the type on both key columns first, inside Power Query, where it is two clicks.
Merge or append?
They sound similar and do opposite things. Merge adds columns — the same rows with more information. Append adds rows — two tables of the same shape stacked on top of each other. If you are combining twelve monthly files, you want Append, or the folder feature that does it for you.
Questions people ask
What is the Power Query equivalent of a SQL join?
Merge Queries. It offers inner, left outer, right outer, full outer, left anti and right anti joins, using the same names and meanings as SQL.
What is an anti join for?
It returns only the rows that did NOT match. Run in both directions it gives you a complete reconciliation — what is in your list and not theirs, and the reverse.
Why does my merged table have more rows than I started with?
The other table has duplicate keys, so each duplicate multiplied its row. That is correct join behaviour. Remove duplicates from the lookup table first.
Why does the merge match nothing?
Usually the key is text on one side and a number on the other, or there are invisible characters. Set both key columns to Text and trim them inside Power Query.
Is a merge faster than thousands of lookups?
Yes, substantially. Lookup formulas recalculate on every change; a merge is computed once when you refresh.
What is the difference between Merge and Append?
Merge adds columns — the same rows with more information. Append adds rows — two tables of the same shape stacked together.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.