Table.Distinct Function in Power Query M (4 Easy Examples)

If you want to remove duplicate rows from a table in Power Query, or keep only the unique values in one column, Table.Distinct is the function you reach for.

It keeps the first copy of each unique row, drops the rest, and leaves your original row order untouched.

Table of Contents

Syntax of Table.Distinct Function

Table.Distinct(table as table, optional equationCriteria as any) as table

where

table (required, table). The table you want to remove duplicate rows from.
equationCriteria (optional, any). Tells the function which columns decide whether two rows count as duplicates. Pass a column name, a list of column names, or a column plus a comparer. Omit it and every column is used, so only fully identical rows are treated as duplicates.

Returns: a table with the duplicate rows removed. For each set of duplicates it keeps the first row it meets and preserves the original order of the rows it keeps.

In plain terms, you hand it a table and tell it what makes a row a duplicate, and it gives you back the table with the extras stripped out.

Example 1: Remove fully-duplicate rows

You have a contacts list where one person was entered twice, and you want a clean list with no repeated rows.

Here is the starting data:

Name	City
Asha Iyer	Pune
Ravi Menon	Delhi
Asha Iyer	Pune
Karan Shah	Mumbai

Call Table.Distinct with no second argument:

let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    Result = Table.Distinct(Source)
in
    Result

With no equationCriteria, every column is used to compare rows, so only the row that repeats across all columns is dropped.

The result keeps three rows:

Name	City
Asha Iyer	Pune
Ravi Menon	Delhi
Karan Shah	Mumbai

The second Asha Iyer / Pune row is gone. The first one stays because Table.Distinct keeps the earliest occurrence.

Example 2: Remove duplicates based on a single column

This time you want one row per city. A couple of people share a city, so the one who appears first wins.

Here is the starting data:

Name	City
Asha Iyer	Pune
Ravi Menon	Delhi
Karan Shah	Pune
Neha Rao	Delhi
Vikram Das	Chennai

Pass the column name as the second argument:

let
    Source = Excel.CurrentWorkbook(){[Name="ByCity"]}[Content],
    Result = Table.Distinct(Source,"City")
in
    Result

Uniqueness is now decided by City alone, so the first row for each city survives and any later row with a city already seen is dropped.

The result keeps three rows:

Name	City
Asha Iyer	Pune
Ravi Menon	Delhi
Vikram Das	Chennai

Karan Shah drops because Pune was already taken by Asha Iyer, and Neha Rao drops because Delhi was already taken by Ravi Menon.

Example 3: Remove duplicates based on multiple columns

One column isn’t always enough. Say a row should count as a duplicate only when both City and Plan match an earlier row.

Here is the starting data:

Name	City	Plan
Asha Iyer	Pune	Pro
Ravi Menon	Pune	Pro
Karan Shah	Pune	Basic
Neha Rao	Delhi	Pro
Vikram Das	Delhi	Pro

Pass a list of column names:

let
    Source = Excel.CurrentWorkbook(){[Name="ByCityPlan"]}[Content],
    Result = Table.Distinct(Source,{"City","Plan"})
in
    Result

Each row is matched on the City and Plan combination, so Pune / Pro and Pune / Basic count as different rows.

Three rows survive:

Name	City	Plan
Asha Iyer	Pune	Pro
Karan Shah	Pune	Basic
Neha Rao	Delhi	Pro

Ravi Menon drops because his Pune / Pro repeats Asha Iyer, and Vikram Das drops because his Delhi / Pro repeats Neha Rao.

Example 4: Ignore letter case with a comparer

By default the match is case-sensitive, so RAVI@mail.com and ravi@mail.com would be treated as two different emails. To collapse them, pass a comparer alongside the column name.

Here is the starting data:

Name	Email
Asha Iyer	asha@mail.com
Ravi Menon	RAVI@mail.com
Asha I.	ASHA@mail.com
Ravi M.	ravi@mail.com
Meera Nair	meera@mail.com

Wrap the column and Comparer.OrdinalIgnoreCase in a list:

let
    Source = Excel.CurrentWorkbook(){[Name="ByEmail"]}[Content],
    Result = Table.Distinct(Source,{"Email",Comparer.OrdinalIgnoreCase})
in
    Result

Comparer.OrdinalIgnoreCase makes the email match ignore case, so rows that differ only in capitalization are seen as the same.

That leaves three rows:

Name	Email
Asha Iyer	asha@mail.com
Ravi Menon	RAVI@mail.com
Meera Nair	meera@mail.com

Asha I. and Ravi M. drop because their emails match earlier rows once case is ignored. Each kept row is, again, the first one of its kind.

Things to keep in mind with Table.Distinct

Matching is case-sensitive by default. Apple and apple count as two values unless you pass Comparer.OrdinalIgnoreCase (Example 4). Comparer.FromCulture handles culture-aware matching.
Column names must match exactly. A typo or wrong case in the column name throws Expression.Error: The column 'City' of the table wasn't found. Check the header text in your source.
It removes duplicates, it doesn’t flag them. If you want to see which rows were duplicated rather than drop them, group the table instead and count the rows per key.

If you are new to all this, the Remove Duplicates in Power Query guide covers the editor button that wraps this function, and Getting Started with Power Query walks through the basics. Cleaning blanks first? See Remove Null Values in Power Query.

Performance and query folding

Against a foldable source like SQL Server, Table.Distinct with no comparer can fold to a SELECT DISTINCT (or a GROUP BY) and run on the server, which is fast even on large tables.

Adding a comparer such as Comparer.OrdinalIgnoreCase usually breaks folding, so the dedupe runs locally. On Excel, CSV, and folder sources it always runs locally, which is fine for the typical table size.

Common questions about Table.Distinct

How do I keep the last duplicate instead of the first?

Table.Distinct always keeps the first occurrence, so sort the table descending on a tiebreaker column first (a date or an ID), then call Table.Distinct. The row you want is now on top, so it becomes the one that survives.

List of All Power Query Functions

Related Power Query Functions / Articles: