Skip to content

List.Distinct in Power Query: 11 Examples

List.Distinct Featured Image

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

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.

Remove duplicates uses Table.Distinct

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

List.Distinct is used in list tools Remove Duplicates

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"}
List Query used for examples

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.

Table used to demonstrate List.Distinct

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])
List.Distinct function returns a list

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.

Table.Distinct function returns a Table

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:

Related Functions