Table.IsDistinct

What is the Table.IsDistinct M function?

The Table.IsDistinct M function is a Power Query function that takes a table as its input and returns a table that contains only the unique rows from the original table. This means that any rows that contain identical values in all columns will be removed, leaving only one instance of each unique row.

The syntax for the Table.IsDistinct function is as follows:

The first parameter, `table`, is the table that you want to check for duplicates. The second parameter, `equationCriteria`, is an optional parameter that allows you to specify the criteria for comparing rows. Finally, the third parameter, `columnsToCompare`, is also an optional parameter that allows you to specify which columns should be used in the comparison.

How does the Table.IsDistinct M function work?

The Table.IsDistinct M function works by first creating a list of all the rows in the original table. It then compares each row in this list to every other row, using the specified criteria and columns. If two rows are found to be identical, only one of them is kept in the final output table.

To illustrate this, consider the following example:

In this example, we first create a table called `Source` that contains three rows of data. The Table.IsDistinct function is then used to create a new table called `DistinctTable`, which contains only the unique rows from the `Source` table. In this case, the `DistinctTable` contains only two rows: one for John Doe and one for Jane Doe.

Using the equationCriteria parameter

As mentioned earlier, the Table.IsDistinct function also allows you to specify the criteria for comparing rows using the `equationCriteria` parameter. This parameter can take any value that can be used in a comparison operation, such as a function or an operator.

For example, consider the following code:

In this example, the `equationCriteria` parameter is set to `each [_],[First Name] = [_],[Last Name]`. This tells the Table.IsDistinct function to compare each row using only the `First Name` and `Last Name` columns. As a result, the `DistinctTable` only contains two rows: one for John Doe and one for Jane Doe.

Using the columnsToCompare parameter

The `columnsToCompare` parameter allows you to specify which columns should be used in the comparison. This parameter takes a list of column names, and only those columns are used in the comparison.

For example, consider the following code:

In this example, the `columnsToCompare` parameter is set to `{“First Name”, “Last Name”}`. This tells the Table.IsDistinct function to compare each row using only the `First Name` and `Last Name` columns. As a result, the `DistinctTable` only contains two rows: one for John Doe and one for Jane Doe.

The Table.IsDistinct M function is a valuable tool for data cleaning and analysis. It allows you to easily identify and remove duplicate values from a table, saving you time and effort. By understanding the syntax and parameters of this function, you can leverage its power to streamline your data analysis workflows.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us