Brantwood Catering Supplies builds its monthly sales report in Power Query: the order file, merged to the price list, merged to the customer master, expanded, loaded. September's report totalled £37,645.50. The orders behind it totalled £28,926.75.
The order file was twelve lines. The report came back thirteen rows long, and nobody counts the rows of a report that refreshes in four seconds.
Rows had been added, and then rows had been taken away. The two faults were in different steps, had nothing to do with each other, and did not cancel.
Orders 12 lines 28,926.75
after the price merge 15 rows 41,796.75
after the customer merge 13 rows 37,645.50
Three of the twelve lines carry SKU PRD-118. The price list holds PRD-118 on two rows — the January price and the August increase, added as a new row rather than replacing the old one. A merge matches every row on the right, not the first one, so expanding wrote each of those three lines out twice.
The second merge pushed the other way. Its join kind was left on Inner, which keeps only rows that matched on both sides. Two September orders belonged to accounts opened that month and not yet on the customer master, so both lines left the report.
| What the report was asked | It answered | The truth |
|---|---|---|
| Order lines | 13 | 12 |
| September sales | £37,645.50 | £28,926.75 |
| Lines carrying PRD-118 | 6 | 3 |
| Customers with sales | 4 | 6 |
£12,870.00 of the total is a copy of itself. £4,151.25 of real September business is not in the report at all, and the two new accounts it belonged to went a month without a statement.
The query did not fail. It refreshed in four seconds, every column had a value in it, and the only number that could have raised a hand was a row count nobody had written down.
01Thirteen Rows From a Twelve-Row File
Twelve Order Lines, Thirteen Rows in the Report
September's order file is twelve lines: the line reference, the SKU, the customer account, and what the line was worth. The last three columns are what Power Query did with each line. The price list holds SKU PRD-118 on two rows, one per price period, so the three lines carrying that SKU were each written out twice when the merge was expanded — £12,870.00 of sales counted a second time. The second merge, against the customer master, was left on an Inner join, and the two lines belonging to accounts opened in September matched nothing, so £4,151.25 of real business was dropped. Every figure is in pounds. The twelve lines total £28,926.75, the report totals £37,645.50, and the gap of £8,718.75 is two mistakes pulling in opposite directions and not quite cancelling.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Eight of the twelve lines behaved perfectly. That is what makes a merge fault hard to see: the file still reconciles line by line wherever you happen to look.
Column E is the one that matters. A left join against a unique key returns 1 for every row, and this merge returned 2 three times and 0 twice.
Scenario: Open the last report you built from a merge and compare its row count with the source table's. Put =ROWS(Report) in one cell and =ROWS(Orders) beside it. They should be the same number, and if they are not, the merge is the reason.
02A Merge Can Add Rows, a Lookup Cannot
Merge Queries does not fetch a value. It adds a column in which every cell holds a table — the rows of the second query that matched that row's key — and nothing has changed shape yet.
The expand step is where rows appear. Expanding writes one output row for every row inside those nested tables, so a key matching twice produces two rows, and a key matching six times produces six.
XLOOKUP cannot do that to you. It returns the first match and one value per formula, so a duplicated key in the lookup table gives you a wrong number rather than extra rows, which is a different problem and a quieter one.
What this covers. Merge Queries is in Excel 2016 and later under Data ▸ Get Data, and in Excel 2010 and 2013 as the free Power Query add-in. The join kinds, the expand dialog and fuzzy matching behave the same way in Power BI.
XLOOKUP,FILTER,UNIQUEandLETneed Microsoft 365 or Excel 2021.VLOOKUP,INDEX,MATCH,COUNTIF,COUNTIFS,SUMIFS,SUMPRODUCTandIFERRORwork in every version this century.
Scenario: Merge two of your own queries and stop before expanding. Click the white space beside the word Table in one of the new cells and read the preview underneath. If it shows more than one row, that key is about to become more than one row.
Try it in the grid03The Six Join Kinds in One Table
The join kind dropdown sits at the bottom of the Merge dialog and defaults to Left Outer. It decides which rows survive, before any expanding happens at all.
| Join kind | What comes through | Rows here |
|---|---|---|
| Left Outer | Every order line; nulls where nothing matched | 12 |
| Inner | Only lines that matched a customer | 10 |
| Right Outer | Every customer; nulls where nothing ordered | 13 |
| Full Outer | Both sides, nulls both ways | 15 |
| Left Anti | Only lines that matched nothing | 2 |
| Right Anti | Only customers that ordered nothing | 3 |
The counts above are the twelve order lines merged to a customer master of seven accounts, four of which ordered in September. Row counts move with both tables, which is why they are worth writing down.
Left Outer is the one you want for a report built on the left table. It is the behaviour of a lookup: every row you started with is still there, and the ones that found nothing come through as nulls.
Left Anti is the exception list nobody builds. Merge the same two tables a second time with Left Anti and the result is exactly the rows that matched nothing — here, two order lines and two account codes that need opening on the master.
Scenario: Duplicate your merge query, change the join kind to Left Anti, and load it to a sheet. If it loads empty, every key matched. If it loads rows, you are looking at the data your report is silently dropping.
Try it in the grid04Inner Joins Drop Rows Without Saying So
An Inner join is the right tool when a missing match means the row does not belong — a payment with no invoice, a scan with no job. It is the wrong tool when a missing match means somebody has not updated a master yet.
That is the whole of this fault. CU-77 and CU-82 are real customers with real orders; they are simply newer than the customer master, which is a reference file somebody maintains by hand.
A left join would have brought both lines through with a null customer name, and a null in a name column is visible from across the room. Inner removed the rows, and a row that is not there cannot look wrong.
Nothing in the refresh reports a dropped row. The step count is the same, the refresh time is the same, and the applied steps pane shows "Merged Queries" either way.
Scenario: Open the Merge dialog on an existing query — click the gear beside the Merged Queries step — and read the join kind it is actually set to. If it says Inner and you did not choose Inner deliberately, switch it to Left Outer and watch the row count move.
Try it in the grid05Why One SKU Matched the Price List Twice
A key matches twice because the right-hand table is not what you think it is. Three reasons cover almost every case.
A history table pretending to be a master. The price list carries one row per price period, so PRD-118 has a January row and an August row. It is a perfectly good table; it is just not keyed on SKU.
A genuine duplicate. The same product added twice under two descriptions, or a customer on the master under two account codes after a merger. Here =COUNTIF(Products[SKU],[@SKU])>1 written down the price list itself finds it in one column.
A key that is not unique in the first place. Merging on Region or on Depot rather than on an identifier produces a row for every pairing, which is how a 12-row file becomes 400 rows and the refresh suddenly takes a minute.
Scenario: Before merging, load the right-hand table on its own and group it by the key with Transform ▸ Group By ▸ Count Rows. Sort the count descending. Anything above 1 is a key that will multiply rows, and the top of that list is your whole problem.
Try it in the grid06Count the Matches Before You Expand
The check is one cell wide and it belongs in the sheet, not the query:
=SUMPRODUCT(--(COUNTIF(Products[SKU],Orders[SKU])>1))
It returns 3 here: three order lines whose SKU appears more than once in the price list, and therefore three lines the expand step is about to write out twice.
The same shape works on the other side. =SUMPRODUCT(--(COUNTIF(Customers[Code],Orders[Customer])=0)) returns 2 — the lines an Inner join will remove and a Left Outer join will fill with nulls.
Two cells, run before the refresh, and both faults are named before the report is built. Run them on the key columns, not on the numbers, because the numbers are downstream of the fault and will look plausible either way.
If the right-hand table genuinely should be one row per key, fix it there. Transform ▸ Remove Duplicates on the key column — or Table.Distinct in the formula bar — and the merge goes back to behaving like a lookup.
Scenario: Put both COUNTIF checks beside your own merge's source tables and give each a name, DupKeys and MissingKeys. Then write =IF(DupKeys+MissingKeys=0,"Merge is safe","Check the keys") in the cell above the report.
07Aggregate Instead of Expanding Rows
Sometimes the right table genuinely has many rows per key and you do not want them all. One customer, forty orders: expanding gives forty rows and you wanted a total.
The expand dialog has a second tab for exactly this. Choose Aggregate rather than Expand and pick Sum, Count or Average of a column, and the result is one output row per input row with the aggregate beside it.
That is the Power Query equivalent of SUMIFS, and it is the step most people reach for the hard way — expanding to forty rows, then grouping back down to one, which is twice the work and loses any column that did not survive the grouping.
=SUMIFS(Orders[Line value],Orders[Customer],A2)
Use the formula when the answer belongs on an existing sheet and the aggregate in the merge when it belongs in the loaded table. Neither one can change your row count, which is the point of both.
Scenario: Take a merge of yours that expands a detail table and open its expand dialog again. Switch to Aggregate, pick Sum of the value column, and compare the row count before and after. Then write the SUMIFS that gives the same answer.
08Merge Is Case Sensitive, VLOOKUP Is Not
Power Query compares text exactly. prd-104 and PRD-104 are two different keys to a merge and the same key to VLOOKUP, XLOOKUP and COUNTIF, every one of which ignores case.
So a file that lookups have handled correctly for years can come through a merge with nulls down half of it, and the fix is upstream: Transform ▸ Format ▸ UPPERCASE on both key columns before the merge, or Text.Upper in the formula bar.
Type matters just as much. An account code stored as text on one side and as a number on the other matches nothing at all, and the merge dialog will not warn you — it shows a match count under the preview, and that count reading 0 of 12 is the warning.
Trailing spaces behave the same way. Transform ▸ Format ▸ Trim on both key columns costs one step and removes the entire category, which is the same job TRIM does in a worksheet.
Scenario: In the Merge dialog, read the sentence under the preview before you click OK: "The selection matches N of M rows from the first table." If N is not M, stop and look at the keys rather than at the join kind.
Try it in the grid09Fuzzy Matching Is a Decision, Not a Setting
Tick Use fuzzy matching to perform the merge and Power Query will match keys that are merely similar — "Rowan Street Deli" to "Rowan St Deli" — on a similarity threshold you set between 0 and 1.
It is genuinely useful on names typed by people and genuinely dangerous on codes. At 0.8 it will cheerfully match two different customers with similar names, and nothing downstream will ever say which rows were guesses.
Two rules make it safe. Set a transformation table so the known abbreviations are declared rather than inferred, and load the match output with its similarity score so a human can read the low ones.
Never fuzzy match an identifier. A SKU, an account code or an invoice number either matches exactly or does not belong in the same row, and a near miss there is a different product rather than a spelling.
10Five Checks That Catch a Bad Merge
The row count. Before anything else, and the only check that catches both faults at once:
=ROWS(Report)-ROWS(Orders) → 1
On a left join to a unique key that is 0. Here it is 1, which is three rows added and two taken away wearing the same coat.
The duplicate-key count. =SUMPRODUCT(--(COUNTIF(Products[SKU],Orders[SKU])>1)) on the key columns, run before the expand, returns the number of rows about to multiply.
The no-match count. =SUMPRODUCT(--(COUNTIF(Customers[Code],Orders[Customer])=0)) returns the rows an Inner join will remove. Both counts belong next to the report, not in a notebook.
The total. =SUM(Report[Line value])-SUM(Orders[Line value]) compares the loaded table with the source. It is 0 on any honest merge, and £8,718.75 here.
The Left Anti query. A second merge of the same two tables, join kind Left Anti, loaded to a sheet. Empty is a pass, and anything in it is a list of the keys somebody needs to add to a master.
Scenario: Add the row-count check to the sheet above your own loaded table, as =ROWS(Report)-ROWS(Orders), and conditionally format it red when it is not 0. It is the cheapest audit in the workbook.
11Eight Things That Bite
- Leaving the join kind on whatever was last used. The dialog remembers, Inner looks like Left Outer in the applied steps, and only the row count knows.
- Expanding a history table. One row per price period, per rate change or per address is not a master, and merging on its key multiplies every row you own.
- Reading a total to check a merge. A total that is too high by a duplicate and too low by a dropped row is a plausible number, and it was the only number anybody checked here.
- Fuzzy matching a code. A similarity of 0.9 between two account numbers means nothing at all, and the match it produces is silent.
- Expanding then grouping back down. Twice the work and a lost column; the Aggregate tab in the expand dialog does it in one step.
- Assuming the merge ignores case.
VLOOKUPdoes,XLOOKUPdoes, Power Query does not, and the same file behaves differently in each. - Merging on a text key against a numeric one. It matches 0 of 12 rows, fills every column with nulls, and never errors.
- Trusting a four-second refresh. The speed is the query's, not the data's, and nothing about a clean refresh says the rows are the rows you started with.
12Mini Exercises
- Build the two key checks on the sample file:
=SUMPRODUCT(--(COUNTIF(Products[SKU],Orders[SKU])>1))and the=0version against the customer list. Expect 3 and 2. - Merge the orders to the price list, expand, and count the rows before and after. Expect 12 and 15, then find the three lines that doubled.
- Change the customer merge from Inner to Left Outer and filter the expanded customer-name column to nulls. Expect two rows: CU-77 and CU-82.
- Duplicate the customer merge, set its join kind to Left Anti, and load it. It should return the same two lines, £4,151.25 between them, as a list you could send to whoever maintains the master.
- Remove duplicates from the price list on SKU, refresh, and check the total. Expect £28,926.75 once both faults are fixed.
What to Take Away
A merge is not a lookup. A lookup answers one question per row and can only ever give you a wrong value; a merge decides which rows exist, and the expand step can hand you more rows than you started with or fewer.
So treat the row count as the output you check first. A left join to a unique key returns exactly the rows it started with, and any other number is a key you have not looked at.
And check the keys, not the figures. The price list holding PRD-118 twice and the customer master missing two codes were both visible in a single COUNTIF before anything was merged — which is the difference between £28,926.75 and £37,645.50 on a report that refreshed cleanly and reconciled on every line anybody read.