List.Distinct
List function · By Tahir Nas · Examples run in our simulator · Published · Updated
Removes duplicate values from a list.
When to use it
Use it to get the unique values of a list or column, for example the pivot values for Table.Pivot, a count of distinct customers with List.Count, or the options for a parameter list.
Syntax
List.Distinct(list as list, optional equationCriteria as any) as listReturns
A list of distinct values in first-seen order.
Example
List.Count(List.Distinct({"Kite", "Reel", "Kite"}))Result
2
Example: distinct values for a pivot
The distinct values of a column, joined here so you can read them; passed to Table.Pivot, they make new categories become columns automatically.
Text.Combine(List.Distinct(#table({"Region", "Rep", "Amount"}, {{"North", "Ana", 120}, {"South", "Bo", 80}, {"North", "Cy", 200}, {"South", "Ana", 50}})[Region]), ", ")Result
North, South
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
- Count distinct = List.Count(List.Distinct(...)).
Common mistakes
- Counting distinct values before trimming and fixing case.
- Expecting it to sort: order is first-seen.
Exercises that use List.Distinct
- intermediate~8 minPro
Count distinct customers per product (kite shop)
For each product, count how many different customers ordered it.
- advanced~10 minPro
Pivot order statuses into columns (kite shop)
Count orders per region with one column per status (Complete, Cancelled, Pending).
- intermediate~8 minPro
List each customer's products in one cell (kite shop)
One row per customer with their distinct products, sorted alphabetically and separated by commas.
- intermediate~9 minPro
Pivot months into columns, summing repeated entries (bakeries)
Each store can have several sales lines per month. Pivot Month into columns with the total Sales per store and month.
- intermediate~8 minPro
Turn attribute–value rows into one row per record (intake)
A form tool stores answers as (RecordID, Field, Value) rows. Pivot Field so each record becomes one row.
- advanced~12 minPro
Pivot categories and add a total that includes future columns (consultancy)
Pivot expense categories into columns (summing amounts), then add a Total across every category column, including new ones.
Learn more
- Group by with several aggregations lesson
- Group By recipes: count if, concatenate, first and last, top value guide
Related functions
Official documentation: List.Distinct on Microsoft Learn.