Power QueryExercises

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
    Expanded

Result

EmailSourceRecordNoPhone
ana@kites.exampleCRM90161 000 222
ben@reels.exampleWebform140113 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

OrderCriterionDirectionWhy
1PriorityLowest firstCRM (1) is more trusted than webforms (2)
2UpdatedNewest firstRecent data is more likely to be current
3RecordNoLowest firstUnique 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.
Functions in this lesson:Table.GroupTable.SortTable.FirstTable.ExpandRecordColumn

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