Table.Distinct Function in Power Query

The Table.Distinct function removes duplicate rows from a table, ensuring that all remaining rows are distinct. An optional parameter, equationCriteria, specifies which columns of the table are tested for duplication. If equationCriteria is not specified, all columns are tested.

Syntax

Table.Distinct(table as table, optional equationCriteria as any) as table

The function has the following arguments:
• table: The input table from which we want to remove duplicates.
• equationCriteria (optional): A set of columns or conditions that define what makes a row unique. If omitted, all columns are considered.

The function returns a new table with only the unique rows, preserving the structure (column names and data types) of the original table but with fewer rows.

Key Points About equationCriteria

If we don’t specify equationCriteria, Table.Distinct treats all columns as the criteria for uniqueness. This means a row is considered duplicate only if all its values (across all columns) are identical to another row.
If we specify equationCriteria, we provide a list of column names (as text) in curly braces {}. For example, {"Column1", "Column2"} means duplicates will be removed based only on the values in "Column1" and "Column2."
The equationCriteria parameter is powerful because it allows us to focus on the fields that matter for our analysis. For instance, if we’re working with sales data and only care about unique transactions (based on transaction ID), we can specify just that column.

Note: The Table.Distinct function checks for duplicates with case sensitivity.

Example: Remove the duplicates from the entire table.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [CustomerID = 1, Name = "Ashish", Phone = "123-4567", Company = "TCS"], 
      [CustomerID = 1, Name = "Ashish", Phone = "123-4567", Company = "TCS"], 
      [CustomerID = 2, Name = "Katrina", Phone = "987-6543", Company = "TCS"], 
      [CustomerID = 3, Name = "Alia", Phone = "543-7890", Company = "TCS"], 
      [CustomerID = 3, Name = "alia", Phone = "543-7890", Company = "TCS"], 
      [CustomerID = 4, Name = "Vicky", Phone = "676-8479", Company = "TCS"], 
      [CustomerID = 4, Name = "Vicky", Phone = "676-8479", Company = "TCS"], 
      [CustomerID = 5, Name = "Mohini", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 6, Name = "Meenakshi", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 7, Name = "Esha", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 7, Name = "Esha", Phone = "574-8864", Company = "TCS"], 
      [CustomerID = 8, Name = "Anjali", Phone = "574-8864", Company = "TCS"],
      [CustomerID = 6, Name = "Meenakshi", Phone = "574-8864", Company = "TCS"]
    }
  ), 
  Return = Table.Distinct(MyTable)
in
  Return

The output of the above code is shown below:

Removing Duplicates from a Single Column If we want to remove duplicates based on a single column, we can specify the column name in the second argument of the function.
= Table.Distinct(MyTable, “Name”)

Previous Next