Power QueryExercises

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

ComparedRows left
Whole rows3
Email only2

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
    Expanded

Result

EmailSignedPlan
ana@kites.example2026-10-12Pro
ben@reels.example2026-09-03Free

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Table.Max returns the whole row (a record) with the latest Signed date in each group.

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.
Functions in this lesson:Table.DistinctTable.GroupTable.MaxTable.ExpandRecordColumn

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.