Power Query Merge Creates Duplicate Rows: Find the Matching Keys First

Explain the six rows before trying to delete them

This example uses Excel for Microsoft 365 on Windows with two local Excel Tables named Sales and Addresses. customer_id is text in both. Addresses contains two different addresses for C10; neither row is an accidental duplicate. The requested merge key is customer_id, so both addresses satisfy the same customer match.

S1 has two matching address records and S2 has the same two matches. Those two sales therefore create four sale-address pairs. S3 creates one pair. A left outer merge also retains S4 even though C99 has no address. This arithmetic accounts for all six output rows. It also explains the inflated total: the extra copies of S1 and S2 add 30 to the original 42.

Reproduce the merge and inspect its nested matches

Create the two source Tables from sales-input.csv and addresses-input.csv. Import each with Data > From Table/Range, set the identifier fields to Text, and use Close & Load To > Only Create Connection. Keep the source Tables separate from the final output Table so a refresh cannot ingest its own generated rows.

1. Open the Sales query. Choose Home > Merge Queries > Merge Queries as New, then select Addresses as the second table.
2. Select customer_id in both previews and choose Left Outer. Do not select fuzzy matching for these structured IDs.
3. Before expanding the new Addresses column, add a Custom Column named address_matches with the expression Table.RowCount([Addresses]). The expected counts are 2, 2, 1 and 0 for S1 through S4.
4. Expand address_id and city from Addresses. Keep the original sale_id so repeated sales are visible. Sort by sale_id and address_id for comparison with the expected CSV.
5. Inspect the six output records. Do not treat a successful load as proof that the joined amount column can still be summed as one amount per sale.

Choose the output grain that the report actually needs

If the report needs one row per sale with a single current address, the reference data must define which address is current. Obtain an explicit status, effective-date interval, or a sales shipping_address_id. Joining only on customer_id cannot supply a distinction that the data does not contain. A unique address key is useful only when it truly identifies the address recorded for that sale.

If the report needs all addresses, six rows are correct. Keep sale-level totals in the original Sales query or aggregate at sale_id before adding them to an address-level report. Do not sum a repeated sale amount across addresses unless you have a documented allocation rule. In this sample, no allocation rule is supplied, so inventing half an amount for each address would be an unsupported change.

Use match counts when the task is only existence

Sometimes the real request is simply to label sales with an address on file. Stop before expansion and retain sale_id, customer_id, amount, and address_matches. Add a status of matched when the count is greater than zero and missing otherwise. This preserves four sales and the original amount total of 42. The separate match count still alerts you that C10 has multiple candidate addresses.

To audit the reference table independently, create a reference to Addresses, use Group By on customer_id with Count Rows, and filter counts greater than one. That audit identifies C10 without deleting A1 or A2. Grouping keys for an existence list is appropriate because only membership matters there; grouping full address records and arbitrarily keeping a city answers a different question.

Check composite keys and apparently identical IDs

An ID may be unique only within a company, warehouse, or year. If the declared key is company plus customer_id, select both columns in the same order on each side of the merge. First confirm the combined key is unique in the intended reference table. Adding arbitrary columns merely until the output becomes shorter can hide a modelling error.

Our sample uses exact text keys without case or whitespace normalization. For a production source, state whether C10 and c10 should be equivalent and whether surrounding spaces are accidental. Apply the agreed cleaning before both the duplicate audit and the join. Keep the original ID available for tracing, and do not convert zero-prefixed codes to numbers to make them match.

Validate refreshes using more than a row count

Keep three independent controls: source sale count, source amount total, and a distribution of address_matches. For this input they are four, 42, and two sales with two matches, one with one match, and one with zero. The expanded result should contain six records, but that count is a diagnostic result rather than a target to force.

As a deliberate refresh test, add address A4 for C10 to the Addresses Table. The expected expanded count becomes eight: three rows for each C10 sale, one for C20, and one for C99. The four-row existence query should remain four rows and total 42. If either source count grows unexpectedly without such a change, check source ranges, appended files, and whether the query output was accidentally included in its input.