Compare & combine files · 8 min read

Power Query Merge Creates Duplicate Rows? Fix Extra Rows

An Excel query with four sales can become six rows after you expand a merge. This is often a valid consequence of the selected keys, not a random refresh bug. The practical question is whether one output row should represent a sale, an address, or a sale-address pair.

JHSOFT editorialUpdated October 19, 2026

WORKED EXAMPLESynthetic data

sale_id,customer_id,amount
S1,C10,10
S2,C10,20
S3,C20,5
S4,C99,7

The four sales total 42. A left outer merge to three address records expands to six rows: both C10 sales appear twice, C20 once, and unmatched C99 once with empty right-side values. Summing the repeated amount column gives 72, which is not the original sales total.

Download sales-input.csv ↓Download addresses-input.csv ↓Download expanded-expected.csv ↓Download match-counts-expected.csv ↓Download match-count-query.pq ↓Download expanded-merge-query.pq ↓

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.

Quick diagnosis: if the row count rises only after Expand, count nested matches. If it rises before merging, inspect the source or append steps. If it doubles on every refresh even when the source is unchanged, check whether an output table or exported result is being read back into the input. The six-row example below isolates match multiplication; it does not diagnose every possible refresh loop.

Illustrated example. Two C10 sales matched with two addresses produce four sale-address pairs, repeating the sale amounts.

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.

Open the Sales query. Choose Home > Merge Queries > Merge Queries as New, then select Addresses as the second table.
Select customer_id in both previews and choose Left Outer. Do not select fuzzy matching for these structured IDs.
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.
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.
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.

Illustrated example. Keeping nested-table counts preserves one record per sale while exposing multiple address matches.

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.

Fix extra rows by choosing the right merge rule

Changing the join to Left Outer is not a uniqueness fix: it changes which unmatched rows survive. Similarly, replacing the merge with a lookup that returns one match does not establish that the first matching address is the intended one. Resolve the data rule first.

What the report needs	Action before expansion	Check afterwards
One current address per sale	Use the actual address ID, a valid composite key, or an explicit current-record rule	Each sale has at most one right-side match
All sale/address pairs	Keep every valid match and retain sale_id	Six rows here; do not add repeated sale amounts
Whether a customer has an address	Keep the nested-table count without expanding	Four sales and amount total 42
True duplicate copies in a reference export	Review which fields define an identical record before deduplicating	Reconcile removed records; do not drop distinct addresses

Run the match-count query before expanding

Load the two CSV samples into Excel Tables named Sales and Addresses. Keep their exact headers and set the identifier columns to Text before loading. Open Data > Get Data > From Other Sources > Blank Query, then Advanced Editor, and paste match-count-query.pq. This version reads the two named worksheet Tables, not other queries or a folder. If a query already has the same name as a source Table, the names here still refer to Tables returned by Excel.CurrentWorkbook.

The result is four rows with address_matches 2, 2, 1 and 0. When you need all sale/address pairs, use expanded-merge-query.pq as a separate query. It produces the six rows in expanded-expected.csv. Keep both outputs outside the source Tables; never name an output Table Sales or Addresses.

Complete M query: four sales with their match counts

let
    SalesSource = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    AddressSource = Excel.CurrentWorkbook(){[Name="Addresses"]}[Content],
    Sales = Table.TransformColumnTypes(SalesSource, {{"sale_id", type text}, {"customer_id", type text}, {"amount", type number}}),
    Addresses = Table.TransformColumnTypes(AddressSource, {{"customer_id", type text}, {"address_id", type text}, {"city", type text}}),
    Merged = Table.NestedJoin(Sales, {"customer_id"}, Addresses, {"customer_id"}, "Addresses", JoinKind.LeftOuter),
    Counted = Table.AddColumn(Merged, "address_matches", each Table.RowCount([Addresses]), Int64.Type),
    Selected = Table.SelectColumns(Counted, {"sale_id", "customer_id", "amount", "address_matches"}),
    Sorted = Table.Sort(Selected, {{"sale_id", Order.Ascending}})
in
    Sorted

What we tested in the native Excel query engine

On October 5, 2026, the two downloadable queries were executed in Excel 16.0 build 5569.0 through the native M provider. The count-only output retained four sales totaling 42. The expanded output had six rows totaling 72 in its repeated amount column, including the unmatched sale S4. After adding a third C10 address and refreshing, expansion returned eight rows and repeated amounts totaled 102; the count-only query still returned four rows totaling 42.

This verifies the synthetic query behavior and refresh boundary, not every menu click or every Excel version. Compare the downloaded input/expected files and inspect the matches before using your own records.

Common questions

Why did the row count change only after I clicked Expand?

The merge stores matching right-side records in a nested table. Expansion exposes each match as a row, repeating the left fields for every matching record.

Should I remove duplicates from the final result?

Only if your output contract defines those records as duplicates. A1 and A2 are different addresses in this example, so deleting one after the merge would discard valid detail.

How do I keep exactly one row per sale?

Use a reference key that selects the intended single record, or retain a nested-table count when you only need match existence. An ambiguous one-to-many relationship needs a rule, not a cosmetic row deletion.

Does a Left Outer merge prevent duplicate rows?

No. It retains unmatched left rows, but expanding several right-side matches still repeats the left fields. Count nested matches first and make the reference key or output rule explicit.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →