List.Distinct Function in Power Query M (5 Easy Examples)

List.Distinct returns a list with the duplicate values removed, keeping each value once. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want the unique values out of a list, or the distinct entries from a single column, this is the function you reach for.

Table of Contents

Syntax of List.Distinct Function

List.Distinct(list as list, optional equationCriteria as any) as list

where

list (required, list). The list you want the distinct values from.
equationCriteria (optional, any). Controls how two values are compared for equality. It can be a key selector function, a comparer such as Comparer.OrdinalIgnoreCase, or a list holding both. Omit it for the default case-sensitive comparison.

Returns: a list with duplicates removed. An empty list in gives an empty list out.

In plain terms, you hand it a list and it gives the same list back with the repeats taken out.

Example 1: Get the unique values from a list

Pull the unique colors out of a list that has a couple of repeats.

let
    Source = List.Distinct({"Teal","Coral","Teal","Olive","Coral","Mauve"})
in
    Source

Result: {"Teal", "Coral", "Olive", "Mauve"}

Teal and Coral each appear twice in the input, so each is kept once. The values come back in the order they first appear.

Example 2: Comparison is case-sensitive by default

With no equationCriteria, List.Distinct compares values exactly. So different casings count as different values, which catches a lot of people out.

let
    Source = List.Distinct({"Mango","mango","Guava","MANGO","guava"})
in
    Source

Result: {"Mango", "mango", "Guava", "MANGO", "guava"}

Nothing is removed. Mango, mango, and MANGO are treated as three separate values, and the same goes for the two spellings of guava.

Example 3: Ignore case with Comparer.OrdinalIgnoreCase

To treat Mango, mango, and MANGO as the same value, pass Comparer.OrdinalIgnoreCase as the second argument.

let
    Source = List.Distinct({"Mango","mango","Guava","MANGO","guava"},Comparer.OrdinalIgnoreCase)
in
    Source

Result: {"Mango", "Guava"}

Now the casing is ignored, so each fruit survives once. The casing kept is the one from the first occurrence, which is why you get Mango and Guava.

Example 4: Get the distinct values of one column

This is the everyday use: the unique entries of a single table column.

Say you have an Orders table with an OrderID and a Region column.

Here is the starting data:

OrderID	Region
A1001	North
A1002	South
A1003	North
A1004	West
A1005	South
A1006	North

Pull the column out as a list with Source[Region], then dedupe it:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Result = List.Distinct(Source[Region])
in
    Result

Result: {"North", "South", "West"}

The six rows have only three regions between them, so you get those three back, in first-seen order.

Example 5: Multiple nulls collapse to one

null is treated as a value, so several nulls dedupe down to a single null.

let
    Source = List.Distinct({"Active",null,"Pending",null,"Active"})
in
    Source

Result: {"Active", null, "Pending"}

The two nulls become one, and the repeated Active is dropped, leaving three values.

Things to keep in mind with List.Distinct

It is case-sensitive by default. Mango and mango are kept as two values. Pass Comparer.OrdinalIgnoreCase as the second argument to compare without case.
It keeps the first occurrence. The output preserves the order values first appear in the source, so {1,1,2,3,3,3} comes back as {1,2,3}.
It works on a list, not a table. It dedupes the values of one column or literal list. To drop duplicate whole rows from a table, use Table.Distinct instead. For the editor’s point-and-click version, see how to remove duplicates in Power Query.
Nulls collapse to a single null. Multiple null values dedupe down to one, kept in first-seen position.

Common questions about List.Distinct

What is the difference between List.Distinct and Table.Distinct?

List.Distinct removes duplicates from a single list (one column or a literal list) and returns a list. Table.Distinct removes duplicate rows from a whole table, across all columns or a chosen subset, and returns a table. Note that appending tables with Table.Combine keeps every duplicate row, so you often dedupe afterward.

How do I remove duplicates while ignoring case?

Pass Comparer.OrdinalIgnoreCase as the second argument, as in List.Distinct(MyList,Comparer.OrdinalIgnoreCase). Without it the comparison is case-sensitive.

List of All Power Query Functions

Related Power Query Functions / Articles: