Table.PartitionValues

What is Table.PartitionValues?

Table.PartitionValues is a M function that returns a table containing the distinct values of a specified column in a given table. The function takes two arguments: the table to partition and the name of the column to partition. Here is the syntax:

The function returns a table with a single column and one row for each distinct value in the specified column of the input table.

Understanding the M Code Behind Table.PartitionValues

The M code behind Table.PartitionValues is relatively simple. The function uses the Table.Group function to group the input table by the specified column. Here is the M code:

The function takes the input table and column name as arguments and assigns them to the variables “table” and “column”, respectively. The Table.Group function is then used to group the input table by the specified column. The resulting table is assigned to the variable “GroupedTable”.

The Table.Group function takes three arguments: the table to group, a list of columns to group by, and a list of aggregations to perform on each group. In this case, we are grouping by the specified column and using the aggregation function “each _” to return the entire group as a table. The resulting table will have a single column with the same name as the input column.

Finally, the function returns the values of the “Column1” column of the “GroupedTable” variable. This column contains the distinct values of the input column in the input table.

Using Table.PartitionValues in Power Query

Table.PartitionValues can be useful in a variety of data transformation scenarios. Here are some examples:

Removing Duplicates

Suppose you have a table with duplicate values in a column and you want to remove them. You can use Table.PartitionValues to get a table of distinct values in the column and then join it back to the original table to remove duplicates. Here’s how:

This code first loads the source table and assigns it to the “Source” variable. Then, it uses Table.PartitionValues to get a table of distinct values in the “Column1” column and assigns it to the “DistinctValues” variable.

Next, it uses Table.NestedJoin to join the “Source” table to the “DistinctValues” table on the “Column1” column with a left anti join. This will return all rows in the “Source” table that do not have a corresponding row in the “DistinctValues” table, effectively removing duplicates in the “Column1” column.

Finally, the code selects only the “Column1” column from the resulting table and assigns it to the “RemovedDuplicates” variable.

Creating a Parameterized Query

Table.PartitionValues can also be used to create a parameterized query that returns the distinct values in a column of a given table. Here’s how:

This code defines a new function called “GetDistinctValues” that takes a table and a column name as arguments. The function uses Table.PartitionValues to get the distinct values of the specified column in the input table and returns the resulting table.

This function can be used in a parameterized query to return the distinct values of a column in a table. For example:

This code loads an Excel workbook and assigns it to the “Source” variable. Then, it uses the “GetDistinctValues” function to get the distinct values of the “Column1” column in the “Table1” table of the workbook.

Finally, it uses Table.SelectRows to filter the “Table1” table to only include rows where the “Column1” value is equal to the first row in the “GetDistinctValues” table. This effectively creates a parameterized query that returns the rows in the “Table1” table that match the first distinct value in the “Column1” column.

Table.PartitionValues is a powerful M function that can be used in a variety of data transformation scenarios. Understanding the M code behind the function can help you use it more effectively in your Power Query projects.

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

Upcoming Courses

Contact Us