POWER QUERY MERGE TROUBLESHOOTING
Why does a Power Query merge create duplicate rows?
A common reason is not that Power Query “duplicated” a row arbitrarily. It is that the selected join key matches more than one row on the other side, so expanding the merged table correctly returns multiple matches.
Short answer
If a row from table A matches two rows in table B, expanding the merged column can produce two output rows for that one row from A. If your reconciliation is supposed to be one-to-one, duplicate join keys are a cardinality failure that should be investigated before you remove duplicates or rewrite the query.
WHAT IS HAPPENING
A merge returns every row that satisfies the selected join relationship
Power Query merges are based on the columns selected as join keys and the chosen join kind. If the key value A03 appears once in the ledger and twice in the bank data, the ledger row has two matching bank rows. Expanding those matches can therefore produce two result rows.
A01 · 100.00A02 · 200.00A03 · 300.00A01 · 100.00A02 · 200.00A03 · 300.00A03 · 300.00CHECK FIRST
Four things to inspect before changing the query
- Count each join key on both inputs. Identify keys that occur more than once where uniqueness is expected.
- Confirm the intended cardinality. Decide whether the business relationship really is one-to-one. Some legitimate workflows are one-to-many, in which case multiple matches may be expected rather than erroneous.
- Compare row counts before and after expansion. Unexpected row multiplication is evidence that the merge relationship changed.
- Compare matched amount totals. A duplicate key can inflate the output amount because the same source row participates in multiple expanded matches.
DO NOT HIDE THE SYMPTOM
Why “Remove Duplicates” can be the wrong fix
Removing duplicate output rows may make the row count look right while hiding the underlying cause. If the duplicate is a real source-data issue, dropping one occurrence can discard evidence. If the two rows are not actually identical, deduplication can also choose a result without establishing which source record is valid.
For a recurring reconciliation, the safer control is to make key uniqueness explicit: detect duplicate keys, show the affected records, and require a reviewer to resolve whether the source data or the expected relationship changed.
OTHER CAUSES
Not every unexpected row increase is the same problem
Duplicate keys on the right side
One left-side row matches multiple right-side rows. This is the classic one-to-many expansion.
Duplicate keys on both sides
Multiple rows on each side share the same key, creating many-to-many combinations and potentially larger row multiplication.
Join key is too broad
A key such as date or amount alone may legitimately repeat. The selected columns may not uniquely identify the intended transaction.
Expected relationship changed
The workflow may have been designed as one-to-one, while upstream data now represents split payments, grouped postings, or another changed business relationship.
FOR RECONCILIATIONS
Duplicate detection is only one control
A recurring reconciliation should also check unmatched records, amount differences, source/output row counts and totals, required columns, required data types, and retained transformation errors.
Read the full Power Query reconciliation validation checklist →RECONVERIFY
Check duplicate keys and other reconciliation exceptions locally
ReconVerify reads supported reconciliation inputs locally in your browser. It is designed to surface deterministic control failures and exceptions for human review without sending reconciliation source data or row-level financial values to ReconVerify infrastructure.
Try ReconVerifyPrimary reference
Microsoft’s Power Query merge documentation describes how merge operations use selected join columns and join kinds to find matching rows.