List.IsDistinct Function in Power Query

The List.IsDistinct function returns a logical value whether there are duplicates in the list; true if the list is distinct i.e., no duplicates, false if there are duplicate values.

Syntax

The function has the following parameters:
• list: The list to evaluate for uniqueness.
• comparisonCriteria (optional): A function or value that defines how elements are compared (e.g., case sensitivity or custom logic). If omitted, the default comparison is case-sensitive and uses exact equality.

Some other key points of the function:
• Null Handling: null values are treated as duplicates (two nulls will return false).
• Empty List: If the list is empty, it returns true (no elements to compare).
• Single Element: If the list has only one element, it returns true (no duplicates possible).

Example: Check the list is distinct or not.

Power Query M

let
  source = {"India", "America", "Canada", null, "Australia", "England", "null"}, 
  return = List.IsDistinct(source)
in
  return   

The output of the above code is true, as null and “null” are two different values.

Example: The function is case sensitive.

Power Query M

let
  source = {"India", "America", "Canada", null, "Australia", "England", "null", "australia"}, 
  return = List.IsDistinct(source)
in
  return    

The output of the above code is true, as by default the function is case sensitive so it will consider Australia and australia are two different values.

Example: Case In-Sensitivity.

Power Query M

let
  source = {"India", "America", "Canada", null, "Australia", "England", "null", "australia"}, 
  return = List.IsDistinct(source, Comparer.OrdinalIgnoreCase)
in
  return    

The output of the above code is false. By using Comparer.OrdinalIgnoreCase we make the function case insensitive, so now the function will consider Australia and australia are same values.