List.IsDistinct

List.IsDistinct is a Power Query M function that checks if a list contains any duplicate values. The function returns true if the list is distinct, and false if there are duplicates.

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

Syntax

List.IsDistinct(
   list as list,
   optional equationCriteria as any,
) as logical

Argument	Attribute	Description
list		The list to test for duplicate values.
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.IsDistinct function tests whether a list of values is unique and returns true if that’s the case. If the function runs into duplicates it returns false. You can provide an optional equationCriteria to change how values are compared during the evaluation.

Examples

Let’s look at a few examples of how the List.IsDistinct functions works.

Basic Examples

A basic example could be to test whether the list { "a", "b", "c", true, false } contains duplicate values. Here’s how you can do that:

List.IsDistinct( { "a", "b", "c", true, false } ) // Output: true

In this case, the value returns true as a result since the list is unique. Now if we change this around slightly, we can test another list as follows:

List.IsDistinct( { "a", "b", "c", true, true } ) // Output: false

With this new test, the function receives a list with two true values and returns false.

Adding an equationCriteria

In these basic examples, we looked at the values as they came in. There are cases, however, where you may want to change the way the list is tested for unique values. For instance, suppose you have a list where some letters are capitalized and others are in lowercase: { "A", "a", "c", "C" }. For some scenarios, you want the evaluation to indicate these values are unique, and therefore return true. By default the List.IsDistinct function compares in that way by using the Comparer.Ordinal function:

// Output: true
List.IsDistinct( { "A", "a", "c", "C" } )
List.IsDistinct( { "A", "a", "c", "C" }, Comparer.Ordinal )

Since the lowercase and uppercase letters are considered different here, both statements return true. Now let’s say you want to ignore casing and just test the underlying letters. We can do that by adding the Comparer.OrdinalIgnoreCase function to the second argument.

// Output: false
List.IsDistinct( { "A", "a", "c", "C" }, Comparer.OrdinalIgnoreCase )

With this expression both "A" and "a", and "c" and "C" are considered identical. You may even have a scenario where the local culture conventions impact the equality of values. For instance:

List.IsDistinct( { "Færdig", "Faerdig" }, Comparer.FromCulture( "en-US" ) ) // Output false
List.IsDistinct( { "Færdig", "Faerdig" }, Comparer.FromCulture( "da-DK" ) ) // Output true

The text value "æ" is considered identical to "ae" in English, but that’s not the case in the Danish language.

Related functions

Other functions related to List.IsDistinct are:

• List.AllTrue
• List.AnyTrue
• List.IsEmpty
• List.MatchesAll
• List.MatchesAny

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