Table.Distinct
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Removes duplicate rows, optionally comparing only some columns.
When to use it
Use it to remove exact duplicate rows, or to keep one row per key by listing key columns. Clean the key columns first (trim, case) so near-duplicates are caught. When which row survives matters, sort first and buffer.
Syntax
Table.Distinct(table as table, optional equationCriteria as any) as tableParameters
- equationCriteria
- A column name or list of columns to compare. Omit to compare whole rows.
Returns
The table with duplicates removed (first occurrence kept).
Example
Table.Distinct(#table({"Region", "Product", "Qty"}, {{"North", "Kite", 2}, {"South", "Reel", 5}, {"North", "Reel", 1}, {"West", "Kite", 4}}), {"Product"})Result
| Region | Product | Qty |
|---|---|---|
| North | Kite | 2 |
| South | Reel | 5 |
Example: whole-row duplicates
Without a column list, two rows are duplicates only if every column matches.
Table.Distinct(#table({"Order", "Qty"}, {{1, 2}, {1, 2}, {1, 3}}))Result
| Order | Qty |
|---|---|
| 1 | 2 |
| 1 | 3 |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Clean text first: "Kite" and "kite " are different values.
Common mistakes
- Deduplicating before cleaning text, so "ana" and "Ana" both survive.
- Relying on which duplicate is kept without sorting first.
The matching command 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, with a case-sensitive comparison.
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).
Exercises that use Table.Distinct
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- 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.
- 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.
- 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.
- intermediate~7 minPro
Append two exports that overlap (kite shop)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
Learn more
Related functions
Official documentation: Table.Distinct on Microsoft Learn.