Remove duplicates in Power Query

In short: select the column or columns that together should be unique, then Home > Remove Rows > Remove Duplicates. Power Query keeps the first row for each value. The M formula looks like Table.Distinct(Source, {"Customer ID"}).

Try it now

This customer list was exported twice, and one customer moved in between. In the end, every customer ID should appear only once.

Task The customer list was exported twice. Each customer ID should appear only once, the first row per customer stays.

	A	B	C
1	Customer ID	Name	City
2	K-101	Anna Schmidt	Berlin
3	K-102	Ben Roth	Hamburg
4	K-101	Anna Schmidt	Berlin
5	K-103	Carla Weber	Leipzig
6	K-102	Ben Roth	Bremen
7	K-104	Dirk Vogel	Berlin
8	K-103	Carla Weber	Leipzig

Did you select every column at first? Then Ben Roth stays twice, because his two rows differ in the city. Power Query compares only the selected columns, and a row counts as a duplicate only when all selected values match.

Which columns should you select?

Goal	Select	Result
drop fully identical rows	all columns	only exact copies are removed
each customer once	the customer ID only	first row per customer stays, even if other columns differ
each combination once	for example customer and product	one row per customer and product

Which row survives depends on the row order. To keep the newest entry on purpose, sort by date descending before removing duplicates. Microsoft notes that the order is not guaranteed in every case unless the table is buffered, so double-check the result on lists that matter.

Watch out: Power Query is case-sensitive

This is where Power Query differs from Remove Duplicates on the Excel ribbon. Microsoft's Power Query documentation states plainly that it considers the case of the text when working with duplicate values. To Power Query, "Anna@Firma.de" and "anna@firma.de" are two different values, and so is an address with a trailing space. The fix: standardize first, then remove duplicates.

Task Each email address should appear only once. Careful: Power Query is case-sensitive, and some addresses have spaces at the edges. Clean the addresses (lowercase, no edge spaces), then remove duplicates by Email.

	A	B
1	Email	Source
2	anna@firma.de	Website
3	Anna@Firma.de	Trade fair
4	ben@shop.de	Website
5	BEN@shop.de	Trade fair
6	carla@web.de	Website
7	dirk@web.de	Trade fair
8	anna@firma.de	Trade fair

This exercise is part of Premium.

Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.

See Premium Already Premium? Log in

The first exercise in this lesson is free.

Step order matters. If you remove duplicates first and clean afterwards, the same addresses end up in the list twice. The data cleaning lesson covers Trim and lowercase in more detail.

The M code of the second exercise

= Table.TransformColumns(Source,{{"Email", Text.Lower, type text}})
= Table.TransformColumns(#"Lowercased Text",{{"Email", Text.Trim, type text}})
= Table.Distinct(#"Trimmed Text", {"Email"})

The list in curly braces at the end of Table.Distinct holds exactly the columns you selected. Without that list, Power Query compares every column.

Power Query or the ribbon command?

• Data > Remove Duplicates on the worksheet deletes rows immediately and for good. Fine for a one-time cleanup.
• Power Query leaves the source untouched and repeats the cleanup on every refresh. Best for lists that arrive again and again.
• UNIQUE as a formula shows a de-duplicated list right next to the data, in Excel 2021 and Microsoft 365.

Show duplicates instead of removing them

Sometimes you want to look at the duplicates before deleting anything. The same menu has Keep Duplicates, which leaves only the rows whose values appear more than once. Another option is Group By with Count Rows: every value with a count above 1 is a duplicate.

Common mistakes

Too few duplicates are found

Usually it is edge spaces or mixed case. Trim and lowercase the column first. Numbers stored as text in one row and as numbers in another also count as different values, so set the data type before you de-duplicate.

Too many rows disappear

Not enough columns were selected. Check the formula bar to see which columns are listed in Table.Distinct.

In German Excel

The German editor calls the command Duplikate entfernen under Start > Zeilen entfernen, and the step shows up as "Entfernte Duplikate". The M function Table.Distinct is the same in every language.

Which Excel versions support this?

Remove Duplicates is part of the Power Query Editor in Excel 2016 and later for Windows and in Microsoft 365. On a Mac it needs Microsoft 365. See the Power Query overview for details.

FAQ