Power Query Remove Duplicates: Keep the Latest or First Row

Power Query's Remove Duplicates is a repeatable query step that re-runs every time you refresh, unlike the Data ribbon's Remove Duplicates button, which is a one-time destructive edit to the worksheet. The ribbon button keeps the first occurrence in sheet order. Power Query usually keeps the first row it encounters for each key, but Microsoft doesn't guarantee which duplicate survives, even if you sort first. If you need a specific row, such as the most recent one, pick it explicitly with Group By.

How is Power Query's dedupe different from the Remove Duplicates button?

The Data ribbon's Remove Duplicates button permanently deletes rows from the worksheet the moment you click OK, with no memory of how it was configured — run it again next month and you're reconfiguring from scratch. Power Query's Remove Duplicates is one step in a saved query, so it re-applies automatically every time the query refreshes against new source data, and you can see and edit exactly which columns it's checking.

	Data ribbon (Remove Duplicates)	Power Query (Remove Duplicates)
Applies to	Data already sitting in the worksheet	Data as it flows through the query
Repeatable on refresh	No — one-time action	Yes — a saved, re-runnable step
Undo	Ctrl+Z immediately, or never after saving/closing	Delete the step in Applied Steps, any time
Which occurrence is kept	First occurrence in current sheet order	Not guaranteed; usually the first row in query order

How do I remove duplicates in Power Query?

Select the column or columns that define a duplicate (a subset of columns, not necessarily every column), then remove duplicate rows based on just that selection.

1. Select the key column(s), e.g. CustomerID (Ctrl+Click for multiple)
2. Home tab > Remove Rows > Remove Duplicates
   (or right-click the column header > Remove Duplicates)

Selecting only the key column(s) — rather than every column — matters: if you select all columns, two rows only count as duplicates when every field matches exactly, which misses near-duplicate records that differ in a non-key field like a timestamp.

How do I keep the latest row instead of the first?

Group the rows by the key and keep the row with the highest date from each group using Table.Max. Sorting by date descending and then removing duplicates often appears to work, but Microsoft doesn't guarantee that Remove Duplicates (Table.Distinct) keeps the first row or respects an earlier sort. Power Query can skip the sort or push both steps to the data source, so an older row can survive.

1. Click fx next to the formula bar to add a step, then enter
   (use your own previous step name, key column, and date column):

   = Table.Group(#"Changed Type", {"CustomerID"},
       {{"Latest", each Table.Max(_, "Date"), type record}})

2. Click the expand icon on the Latest column header and pick the
   columns to bring back (untick CustomerID, which is already there,
   and "Use original column name as prefix")
3. Set the data types again; expanded columns come back untyped

To keep the earliest row per key instead, use Table.Min in place of Table.Max. To keep the first row in source order, add an index first (Add Column > Index Column) and group with Table.Min(_, "Index"). If two rows tie on the latest date, Table.Max returns only one of them.

No-code alternative: in the Queries pane, right-click the query > Reference to create a second query based on it. In that new query, use Group By on the key column with a "Max" aggregation on the date column to get each key's latest date. Then use Home > Merge Queries to join the original query back in on both the key and the date columns (Ctrl+Click both columns in each table, in the same order), and expand the columns you need. If two rows share the latest date, both come back, so ties show up instead of being dropped silently.

If you keep the sort-then-dedupe pattern, wrap the sorted table in Table.Buffer so Remove Duplicates runs against the buffered, sorted rows:

= Table.Distinct(
    Table.Buffer(Table.Sort(#"Changed Type", {{"Date", Order.Descending}})),
    {"CustomerID"})

Microsoft's Table.Distinct reference recommends buffering the table first if you want duplicate removal to behave predictably, though its Power Query "Preserving sort" troubleshooting guidance says buffering keeps the sort order only "in some cases." Buffering also loads the whole table into memory and stops later steps from folding to the source, so it can be slow on large data. Group By with Table.Max doesn't depend on sort order at all.

Does Power Query's dedupe compare case-sensitively?

Yes — like Power Query's Merge, the default duplicate comparison is case-sensitive, so "Acme" and "ACME" are treated as different values and both survive. Normalize with Text.Upper or Text.Lower on the key column first (Transform > Format > UPPERCASE/lowercase) if the source data has inconsistent casing.

Common mistakes when deduping in Power Query

Most dedupe mistakes come from trusting sort order to decide which row survives, or from doing the cleanup somewhere that won't survive the next refresh.

• Relying on a Sort step (without Table.Buffer) to keep the latest row: Power Query doesn't guarantee Remove Duplicates respects an earlier sort, so an older row can survive. Use Group By with Table.Max, or buffer the sorted table with Table.Buffer before removing duplicates.
• Selecting every column instead of just the key: misses duplicates that differ only in a non-key field (e.g. an updated status column).
• Assuming it's case-insensitive: "Acme" and "ACME" both survive unless you normalize case first.
• Using the worksheet's Remove Duplicates button on a query-fed table: the next refresh reintroduces the duplicates, since the fix lived outside the query. Do the dedupe inside the query instead so it survives refreshes.

For the worksheet-level Remove Duplicates button and formula-based approaches without Power Query, see remove duplicates in Excel. For combining tables before deduping, see Power Query Merge.