In this post, we look at the List.Distinct Power Query M function. It is a selection function that returns a distinct list of items based on a list supplied.

Along with detailed notes, there are 11 examples to show how to use the function in different contexts.

**Table of Contents**:

- Purpose
- Return Value
- Syntax
- Parameters
- Usage Notes
- Examples
- Example #1: Distinct items from a list
- Example #2: Distinct items from a list when ignoring case
- Example #3: Distinct items from a list based on local language comparison
- Example #4: Distinct items from a separate list query
- Example #5: Distinct items from a single Table column
- Example #6: Distinct items from different data types
- Example #7: Count distinct items
- Example #8: Distinct items from multiple lists
- Example #9: Distinct vs unique
- Example #10: List.Distinct vs Table.Distinct
- Example #11: Distinct list of lists

- Related Tutorials
- Related Functions

## Purpose

Filters down a list of items by removing duplicate values. Optional equation criteria can be specified to control the definition of a duplicate (such as ignoring case and local language variations).

## Return Value

Returns all distinct items from a list as a list.

## Syntax

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

## Parameters

**list**– A Power Query list. This list can be:- individual items contained within curly brackets e.g. {“Alpha”,4,null}
- Existing query or step with a list type as an output.
- Function which returns a list.

**evaluationCriteria**– A comparer function used to define duplicates. For example,**Comparer.OrdinalIgnoreCase**is commonly used to ignore case.

## Usage Notes

List.Distinct is a selection function in the List category. It returns a list of all distinct items from a list supplied. The definition of distinct items can be refined using the optional **evaluationCriteria** parameter.

The first distinct value of each group is returned. For example, in the following formula, case has been ignored; therefore, the result is {“Alpha”, “bravo”}. **“Bravo”** is deemed to be a duplicate of **“bravo”**, the duplicate is removed, and the first instance returned.

`= List.Distinct({"Alpha", "bravo", "Bravo"},Comparer.OrdinalIgnoreCase)`

The transformation to remove duplicates from the main user interface (**Home [1] > Remove Rows (drop down) [2] > Remove duplicates [3]**) uses Table.Distinct and **not** List.Distinct.

However, List.Distinct is used for the Remove Duplicates transformation found in the contextual List Tools ribbon (**List Tools [1] > Remove Duplicates [2]**)

The List.Union function combines multiple lists and returns a distinct list. Therefore, if working with multiple lists, List.Union may be a better choice.

The List.IsDistinct function checks if a list contains only distinct items.

If the supplied list is empty, the result is an empty list.

## Examples

### Example #1: Distinct items from a list

The list parameter can be the standard M code syntax, which includes comma-separated items contained in curly brackets.

`= List.Distinct({"Alpha", "Bravo", "Charlie", "alpha", "bravo", "Charlie"})`

In the code above, the second instance of **Charlie** contains an uppercase first character, which is a duplicate of the first instance. Therefore, a list of 5 items is returned.

**Returns: **{“Alpha”, “Bravo”, “Charlie”, “alpha”, “bravo”}

### Example #2: Distinct items from a list when ignoring case

The second argument of List.Distinct is the **evaluationCriteria**. This is an optional argument used to define the meaning of distinct.

In the example below, the **Comparer.OrdinalIgnoreCase **is applied so that it changes the default behavior to ignore case.

```
= List.Distinct(
{"Alpha", "Bravo", "Charlie", "alpha", "bravo", "Charlie"},
Comparer.OrdinalIgnoreCase
)
```

As uppercase and lowercase characters are seen as equal, only the first instance of each item is returned.

**Returns**: {“Alpha”, “Bravo”, “Charlie”}

### Example #3: Distinct items from a list based on local language comparison

The **evaluationCriteria** argument can apply different regional variations to define which items are considered duplicate values.

The word **Ændringer** is a Danish word. English does not have the **Æ** character in the alphabet. In English, the letters **AE** are considered to be equivalent to **Æ**.

The Comparer.FromCulture function declares the language and whether to ignore case (true = ignore case, false = do not ignore case).

```
= List.Distinct(
{"Ændringer", "ændringer", "AEndringer", "aendringer"},
Comparer.FromCulture("en-GB", true)
)
```

In the formula above, **Æ** and **AE** are deemed to be the same. As the case is ignored, only a single list value is returned.

**Returns: **{“Ændringer”}

If we applied the same formula as above, but did not ignore case, the formula and result would be as follows.

```
= List.Distinct(
{"Ændringer", "ændringer", "AEndringer", "aendringer"},
Comparer.FromCulture("en-GB", false)
)
```

**Returns: **{“Ændringer”, “ændringer”}

If we applied the culture as Danish (**da-DK**) rather than English (**en-GB**), **Æ** and **AE** would be different characters. If we also ignored case, the formula and result would be:

```
= List.Distinct(
{"Ændringer", "ændringer", "AEndringer", "aendringer"},
Comparer.FromCulture("da-DK", false)
)
```

**Returns: **{“Ændringer”, “ændringer”, “AEndringer”, “aendringer”}

Finally, let’s see what happens if we apply Danish and ignore the case.

```
= List.Distinct(
{"Ændringer", "ændringer", "AEndringer", "aendringer"},
Comparer.FromCulture("da-DK", true)
)
```

**Returns: **{“Ændringer”, “AEndringer”}

Thanks must be given to Chris Webb, who has provided the most useful resource to understand the **evaluationCriteria** argument: https://blog.crossjoin.co.uk/2017/01/22/the-list-m-functions-and-the-equationcriteria-argument/

### Example #4: Distinct items from a separate list query

So far, all examples used a list contained in curly brackets. But the list can be a query name, where that query returns a list.

The following is a list query called **Example List**.

`= {"Alpha", "Bravo", "Charlie", "alpha", "bravo", "Charlie"}`

The above list is used inside the function, as shown below.

`= List.Distinct(#"Example List")`

In this example, the **evaluationCriteria** argument is not used; therefore, case is not ignored. The last value of **Charlie** is a duplicate and has been removed.

**Returns:** {“Alpha”, “Bravo”, “Charlie”, “alpha”, “bravo”}

### Example #5: Distinct items from a single Table column

Within Power Query, a single table column is a list. Therefore, a table column reference can be the list parameter used inside a function.

The table below is called **Example Table**.

The formula below returns the list of distinct items from the **Item** column of the **Example Table**.

`= List.Distinct(#"Example Table"[Item])`

**Returns:** {“Alpha”, “Bravo”, “Charlie”, “Delta”, “alpha”, “bravo”, “charlie”}

### Example #6: Distinct items from different data types

List.Distinct treats each data type as separate items. In the example below, the case is ignored. As a result, only **“True”** and **“true”** are considered to be the same value.

The logic value of **true** is not deemed to be the same as the text value of **“True”**, and the numeric **3** is not the same as the text **“3”**.

`= List.Distinct({"Alpha", 3, true, null, "3", "True", "true"}, Comparer.OrdinalIgnoreCase)`

**Returns: **{“Alpha”, 3, true, null, “3”, “True”}

### Example #7: Count distinct items

The following example demonstrates using List.Distinct inside List.Count to calculate the number of distinct items.

`= List.Count(List.Distinct({"Alpha", "Bravo", "Charlie", "alpha", "bravo", "Charlie"}))`

**Charlie** is duplicated, which leaves 5 distinct items.

**Returns: **5

### Example #8: Distinct items from multiple lists

Within Power Query, the most common functions for joining lists are List.Union and List.Combine. We consider both below, as they work in different ways.

**List.Union**: Takes a list of lists and returns an output list where duplicate values are removed.

`= List.Distinct(List.Union({{"Alpha", "Bravo"}, {"Bravo", "Charlie", "Delta"}}))`

**Returns:** {“Alpha”, “Bravo”, “Charlie”, “Delta”}

While the result of the formula above is a distinct list, the use of List.Distinct is surplus to requirements; List.Union returns a distinct list by itself.

**List.Combine**: Takes a list of lists and merges them into a single list. The critical difference to List.Union is that List.Combine does not remove any values.

`= List.Distinct(List.Combine({{"Alpha", "Bravo"}, {"Bravo", "Charlie", "Delta"}}))`

**Returns:** {“Alpha”, “Bravo”, “Charlie”, “Delta”}

In the formula above, the result of the List.Combine function is: {“Alpha”, “Bravo”, “Bravo”, “Charlie”, “Delta”}. It is List.Distinct which performs the transformation to remove duplicate values.

### Example #9: Distinct vs unique lists

There is a subtle difference between distinct lists and unique lists. However, the terms are often used interchangeably.

The examples in this section use the list query from Example 4.

A **distinct list** contains a single instance of each item.

`= List.Distinct(#"Example List")`

**Returns:** {“Alpha”, “Bravo”, “Charlie”, “alpha”, “bravo”}

In contrast, a **unique list** contains those items which appear only once in the list. For example {“Alpha”, “Alpha”, “Alpha”} has one distinct item, but zero unique items.

As there is no List.Unique function in Power Query, the example below simulates the result.

```
= List.Accumulate(
List.Numbers(0, List.Count(List.Distinct(#"Example List"))),
{},
(state, current) =>
if List.Count(List.Select(#"Example List",
each _ = List.Distinct(#"Example List"){current}))
= 1
then
List.Combine({state, {List.Distinct(#"Example List"){current}}})
else
state
)
```

**Returns:** {“Alpha”, “Bravo”, “alpha”, “bravo”}

### Example #10: List.Distinct vs Table.Distinct

The examples below compare the List.Distinct and Table.Distinct functions. The parameters used for both are based on **Item** column of the **Example Table** used in Example 5.

**List.Distinct** returns a list as shown below.

`= List.Distinct(#"Example Table"[Item])`

**Returns:** {“Alpha”, “Bravo”, “Charlie”, “Delta”, “alpha”, “charlie”}

**Table.Distinct** uses a similar process to remove duplicates, but returns the full table of corresponding records.

`= Table.Distinct(#"Example Table", {"Item"})`

**Returns: **A table with duplicate items removed from the **Item** column.

### Example #11: Distinct lists of lists

When working with lists of lists, List.Distinct looks at the elements inside each list.

In the formula below, the list {“Alpha”, “Alpha”} appears twice; therefore, the second instance is removed.

```
= List.Distinct({
{"Alpha", "Alpha"},
{"Alpha", "Bravo"},
{"Alpha", "Alpha"},
{"Alpha", "Charlie"}}
)
```

**Returns: ** {{“Alpha”, “Alpha”}, {“Alpha”, “Bravo”}, {“Alpha”, “Charlie”}}

## Related Tutorials

List.Distinct is used in the following Tutorials: