Table.IsDistinct Function in Power Query

The Table.IsDistinct indicates whether the table contains only distinct rows (no duplicates). Returns true if the rows are distinct, false otherwise.

Syntax

Table.IsDistinct(table as table, optional comparisonCriteria as any) as logical

The function has the following parameters:
• table: The table to evaluate.
• comparisonCriteria (optional): We can specify a list of column names (as text) to check for uniqueness. If omitted, all columns are checked.

The Table.IsDistinct Function has the behavior:
• Case Sensitivity: Values are compared case-sensitively (e.g., "Apple" ≠ "apple").
• Null Handling: null values are treated as equal (two nulls are duplicates).
• Empty Table: Returns true (no rows to compare).
• Single Row: Returns true (no duplicates possible).

Example: Check all columns and determine if the table is distinct.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Phone = "123-4567"], 
      [CustomerID = 2, Name = "Katrina", Phone = "987-6543"], 
      [CustomerID = 3, Name = "Alia", Phone = "543-7890"], 
      [CustomerID = 4, Name = "Vicky", Phone = "676-8479"], 
      [CustomerID = 5, Name = "Mohini", Phone = "574-8864"], 
      [CustomerID = 6, Name = "Meenakshi", Phone = "574-8864"], 
      [CustomerID = 7, Name = "Esha", Phone = "574-8864"], 
      [CustomerID = 8, Name = "Anjali", Phone = "574-8864"]
    }
  ),
    #"Duplicate Check" = Table.IsDistinct(MyTable)
// If the name contains space put it in quotes and place # in front of it. Otherwise quotes and # is not placed.
in
    #"Duplicate Check"

In the output the above function returns True as we can see in the following image also:

Table.IsDistinct function in Power Query

Example: Check only “Phone” column determine if the table is distinct in column.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Phone = "123-4567"], 
      [CustomerID = 2, Name = "Katrina", Phone = "987-6543"], 
      [CustomerID = 3, Name = "Alia", Phone = "543-7890"], 
      [CustomerID = 4, Name = "Vicky", Phone = "676-8479"], 
      [CustomerID = 5, Name = "Mohini", Phone = "574-8864"], 
      [CustomerID = 6, Name = "Meenakshi", Phone = "574-8864"], 
      [CustomerID = 7, Name = "Esha", Phone = "574-8864"], 
      [CustomerID = 8, Name = "Anjali", Phone = "574-8864"]
    }
  ),
    #"Duplicate Check" = Table.IsDistinct(MyTable, "Phone")
// If the name contains space put it in quotes and place # in front of it. Otherwise quotes and # is not placed.
in
    #"Duplicate Check"

In the output the above function returns false as we can see in the following image also:

Previous Next