Choose which duplicate to keep with a business rule
Implement a multi-criteria survivor rule that gives the same answer on every refresh.
~10 min read · advanced · Module 9: Duplicates and keys
By Tahir Nas · Examples run in our simulator · Updated
Before this: Remove duplicates on the right key, Check key quality before you rely on it
Real deduplication rules have several criteria: keep the record from the most trusted system; if there's a tie, the most recently updated; if there's still a tie, the lowest record number. Writing the rule down is half the work. The other half is implementing it so that the same data always gives the same survivor.
Sort inside each group, then take the first row
Group by the key with an All Rows-style aggregation, sort each group by the rule's criteria, and take the first row as a record. Because the sort happens inside the group, nothing depends on the row order between steps:
let
Contacts = #table({"Email", "Source", "Priority", "Updated", "RecordNo", "Phone"}, {
{"ana@kites.example", "Webform", 2, #date(2026, 10, 1), 17, "0161 000 111"},
{"ana@kites.example", "CRM", 1, #date(2026, 6, 4), 9, "0161 000 222"},
{"ben@reels.example", "Webform", 2, #date(2026, 9, 9), 21, "0113 000 333"},
{"ben@reels.example", "Webform", 2, #date(2026, 9, 9), 14, "0113 000 444"}
}),
Survivors = Table.Group(Contacts, {"Email"}, {{"Keep", each Table.First(Table.Sort(_, {
{"Priority", Order.Ascending},
{"Updated", Order.Descending},
{"RecordNo", Order.Ascending}
})), type record}}),
Expanded = Table.ExpandRecordColumn(Survivors, "Keep", {"Source", "RecordNo", "Phone"})
in
ExpandedResult
| Source | RecordNo | Phone | |
|---|---|---|---|
| ana@kites.example | CRM | 9 | 0161 000 222 |
| ben@reels.example | Webform | 14 | 0113 000 444 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Ana's CRM record wins on priority even though the webform row is newer. Ben's two rows tie on priority and date, so the lowest RecordNo decides. Every criterion in the rule appears in the sort, and the last one is unique, so there's never an arbitrary choice.
Write the rule as a table first
| Order | Criterion | Direction | Why |
|---|---|---|---|
| 1 | Priority | Lowest first | CRM (1) is more trusted than webforms (2) |
| 2 | Updated | Newest first | Recent data is more likely to be current |
| 3 | RecordNo | Lowest first | Unique tie-breaker so the result is deterministic |
Agree this table with the data owner before building the query. It's also the documentation: anyone reading the query later can check the sort against the rule.
Survivor rules vs merging fields
Sometimes no single row is best: you want the CRM's name but the webform's newer phone number. That's a field-level rule. Group as above, then build each output column with its own expression over the group (for example the phone from the newest row, the name from the highest-priority row).
Where to find this in Excel
- Excel for Windows
- Home > Group By, with the All Rows operation
- What you see
- Each group's rows land in a nested table, which you then sort and pick from with a short formula.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Grouping or summarizing rows (Microsoft Learn).
Common mistakes
- Using Remove Duplicates after a sort and assuming the sorted order decides the survivor.
- Leaving the rule without a unique final tie-breaker, so ties resolve arbitrarily.
- Implementing the rule without agreeing it with the data owner, so 'the right row' means different things to different people.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: Home → Group By with the All Rows operation creates the nested tables; editing the generated formula to add Table.First(Table.Sort(...)) is a small change in the formula bar.
- Large groups are processed in memory by the host; filter and remove unneeded columns before grouping.
- The simulator sorts deterministically and supports Table.First, Table.Sort and record expansion as used here.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Duplicates and keys quiz