Remove duplicates on the right key
Deduplicate by business key and control which row survives.
~10 min read · intermediate · Module 9: Duplicates and keys
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Trim, clean and capitalise text
"Duplicate" depends on the question. Two signup rows with the same email but different dates are duplicates for a mailing list, but not for an audit log. Decide the key first: which columns, taken together, should be unique?
Whole rows vs selected columns
let
Signups = #table({"Email", "Signed"}, {
{"ana@kites.example", #date(2026, 9, 1)},
{"ben@reels.example", #date(2026, 9, 3)},
{"ana@kites.example", #date(2026, 10, 12)}
}),
WholeRows = Table.RowCount(Table.Distinct(Signups)),
ByEmail = Table.RowCount(Table.Distinct(Signups, {"Email"}))
in
#table({"Compared", "Rows left"}, {{"Whole rows", WholeRows}, {"Email only", ByEmail}})Result
| Compared | Rows left |
|---|---|
| Whole rows | 3 |
| Email only | 2 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Select the key column(s) before choosing Remove Duplicates. With no column list, Table.Distinct compares entire rows, so rows that differ in any column all survive.
Which row survives?
Table.Distinct keeps one row per key, but Microsoft's documentation doesn't promise which one. The popular trick of sorting newest-first and then removing duplicates relies on row order surviving between steps, which real hosts guarantee only with Table.Buffer. When it matters which row you keep, choose it by value:
let
Signups = #table({"Email", "Signed", "Plan"}, {
{"ana@kites.example", #date(2026, 9, 1), "Free"},
{"ben@reels.example", #date(2026, 9, 3), "Free"},
{"ana@kites.example", #date(2026, 10, 12), "Pro"}
}),
Latest = Table.Group(Signups, {"Email"}, {{"Row", each Table.Max(_, "Signed"), type record}}),
Expanded = Table.ExpandRecordColumn(Latest, "Row", {"Signed", "Plan"})
in
ExpandedResult
| Signed | Plan | |
|---|---|---|
| ana@kites.example | 2026-10-12 | Pro |
| ben@reels.example | 2026-09-03 | Free |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Removing duplicates on a key is a one-way step: the other rows are gone from the output, and so is any information only they held, such as a second phone number. If those details matter, group by the key and combine them (for example Text.Combine(List.Distinct([Phone]), ", ")) instead of dropping them.
Before deduplicating, count how many rows each key has. If most keys appear once and a few appear dozens of times, the duplicates may be a data-entry problem worth reporting, not just tidying.
Where to find this in Excel
- Excel for Windows
- Select the key columns > Home > Remove Rows > Remove Duplicates
- What you see
- Rows that repeat across the selected columns are dropped. The comparison is case-sensitive.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Working with duplicate values (Microsoft Learn).
Common mistakes
- Removing duplicates on all columns when the business key is one column.
- Relying on sort-then-distinct to keep the latest row without buffering or a value-based rule.
- Deduplicating before cleaning the key text.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: select key columns → Home → Remove Rows → Remove Duplicates writes Table.Distinct with those columns.
- Table.Distinct compares text case-sensitively in both hosts unless you pass a comparer such as Comparer.OrdinalIgnoreCase.
- The simulator keeps the first row of each duplicate group in the current order (compatibility page KD01). Graded tasks never depend on that, and neither should your queries.
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Keep one signup per email address (newsletter)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~6 minPro
Keep one signup per email address (newsletter)
Build a mailing list with one row per email address from signups that contain repeats.
- beginner~6 minPro
Keep one signup per email address (webinar)
Build a mailing list with one row per email address from signups that contain repeats.
- advanced~10 minPro
Keep each person's most recent signup (newsletter)
For every email keep only the row with the latest Signed date, whatever the row order.
- advanced~10 minPro
Keep each person's most recent signup (webinar)
For every email keep only the row with the latest Signed date, whatever the row order.
- beginner~6 minPro
Find invoice numbers that appear more than once (accounts payable)
Before loading, list every invoice number that occurs more than once and how many times.
- beginner~6 minPro
Find invoice numbers that appear more than once (school office)
Before loading, list every invoice number that occurs more than once and how many times.
- intermediate~7 minPro
Count each email once, whatever its case or spacing (petition)
The same address appears as Amara@Mail.example and ' amara@mail.example'. Build a clean list of unique emails.
- intermediate~7 minPro
Count each email once, whatever its case or spacing (raffle)
The same address appears as Amara@Mail.example and ' amara@mail.example'. Build a clean list of unique emails.
- advanced~10 minPro
Keep the highest reading per site and day, with its inspector (air quality)
Sites are inspected several times a day. Keep only the row with the highest reading for each site and day.
- advanced~10 minPro
Keep the highest reading per site and day, with its inspector (pools)
Sites are inspected several times a day. Keep only the row with the highest reading for each site and day.
- intermediate~8 minPro
Flag every order from a repeat customer (kite shop)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- intermediate~8 minPro
Flag every order from a repeat customer (bakery)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- intermediate~8 minPro
Flag every order from a repeat customer (bike rental)
Keep every order, and add Repeat Customer = true on all orders of customers who ordered more than once.
- beginner~5 minPro
List the region and product combinations that sold (kite shop)
Produce one row per Region and Product pair that appears in the orders, sorted.
- beginner~5 minPro
List the region and product combinations that sold (bakery)
Produce one row per Region and Product pair that appears in the orders, sorted.
- beginner~5 minPro
List the region and product combinations that sold (bike rental)
Produce one row per Region and Product pair that appears in the orders, sorted.
- advanced~11 minPro
Replace sort-then-remove-duplicates with a deterministic rule (subscriptions)
The starter sorts newest first and removes duplicates to keep each account's latest status. Power Query doesn't guarantee which duplicate survives; make it explicit.
- advanced~11 minPro
Replace sort-then-remove-duplicates with a deterministic rule (support plans)
The starter sorts newest first and removes duplicates to keep each account's latest status. Power Query doesn't guarantee which duplicate survives; make it explicit.