List.Distinct - List Function

List.Distinct is a Power Query M function that removes duplicate values from a given list. The function returns a new list containing unique values only.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
list		The list to remove duplicates from.
equationCriteria	optional	Uses Comparer Functions to determine how values are equated during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

The List.Distinct function can be used to create a list that includes only the unique elements from the original list. In the case that you provide an empty list as the input, the function will promptly return an empty list as well, maintaining the integrity of your data.

Additionally, the function supports an optional equationCriteria argument. Here you can specify how to perform a comparison. You can make a case-sensitive comparison using Comparer.Ordinal, ignore case with Comparer.OrdinalIgnoreCase and even make culture-sensitive comparisons using Comparer.FromCulture.

Examples

Let’s look at some examples on how you can use the List.Distinct function.

Basic Remove Duplicates Operation

Imagine you have a list of numbers with some duplicate entries and you’d like to clean it up. The List.Distinct function can remove those duplicates, providing you with a list containing only the unique numbers. For example:

// Output: {1, 2, 3}
List.Distinct( {1, 1, 2, 3, 3, 3} )

This will remove any duplicate instances of the numbers 1 and 3, leaving you with a more streamlined list.

Case-Sensitive Comparisons with Comparer.Ordinal

Let’s say you have a list of fruits, and the capitalization varies. If you want to treat “apple” and “Apple” as different entries, Comparer.Ordinal is the option to choose. This will perform a case-sensitive comparison and return distinct values accordingly.

// Output: { "apple", "Apple", "banana" }
List.Distinct( { "apple", "Apple", "banana", "apple" }, Comparer.Ordinal )

This option is particularly useful when case sensitivity is important in your comparison. It’s good to note that when you omit the second argument, the function defaults to using Comparer.ordinal.

But what if your list contains other lists? Can you still remove duplicates? Absolutely. With List.Distinct, you can remove duplicates from a list containing other lists as elements. For instance:

// Output: { {2, 2}, {1, 1} }
List.Distinct( { {2, 2}, {1, 1}, {1, 1} } )

This will examine the inner lists and retain only the unique ones, making it very handy for working with complex, structured data.

Using Comparer.OrdinalIgnoreCase

Sometimes, case sensitivity doesn’t matter, and you just want unique elements irrespective of letter casing. In such scenarios, using Comparer.OrdinalIgnoreCase ensures that you get distinct elements without case sensitivity affecting the output.

// Output: { "apple", "banana" }
List.Distinct( { "apple", "Apple", "banana", "apple" }, Comparer.OrdinalIgnoreCase )

This option works wonderfully when you aim to consolidate similar items, regardless of how they are capitalized.

Using Comparer.FromCulture

Cultural norms can affect string comparisons significantly. For example, in American English, “Færdig” and “Faerdig” might be considered the same. You can specify this with Comparer.FromCulture as follows:

// Output: { "Færdig" }
List.Distinct( { "Færdig", "Faerdig" } , Comparer.FromCulture("en-US") )

This output demonstrates how cultural sensitivities can impact what is considered “distinct.”

However, in Danish culture, “Færdig” and “Faerdig” are considered different. When you use the Danish culture in Comparer.FromCulture, both are retained as distinct.

// Output: { "Færdig", "Faerdig" }
List.Distinct( { "Færdig", "Faerdig" } , Comparer.FromCulture("da-DK") )

This shows the importance of understanding cultural context when working with data, particularly when it involves textual comparisons.

To conclude, by understanding how to effectively leverage the List.Distinct function with its various Comparer options, you can gain precise control over how duplicates are identified and removed in your lists.

Learn more about List.Distinct in the following articles:

Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Distinct are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-distinct