Table.SelectRows

What is Table.SelectRows?

The Table.SelectRows function in Power Query is used to filter rows in a table based on a condition. The function takes two arguments: the table to filter and the condition to apply. The condition is expressed as an expression that evaluates to true or false for each row in the table.

Here is an example of the Table.SelectRows function in action:

In this example, we have a table with two columns, “Column 1” and “Column 2”. We use the Table.FromRows function to create a table from some JSON data. We then use the Table.SelectRows function to filter the table so that only rows where the value in “Column 1” is greater than 2 are returned.

Understanding the M Code

The M code behind the Table.SelectRows function is relatively simple. Here is the M code that is generated when we use the function:

The M code defines a function called Table.SelectRows that takes two arguments, a table and a condition. The function then uses the Table.Select function to filter the table based on the condition, and returns the filtered table.

The condition argument is defined as a function. This function takes a single argument, a row from the table, and returns true or false depending on whether the row should be included in the filtered table.

Using the Table.SelectRows Function

To use the Table.SelectRows function effectively, you need to understand how to construct the condition argument. The condition argument is a function that takes a single argument, a row from the table, and returns true or false depending on whether the row should be included in the filtered table.

Here are some examples of how to construct the condition argument:

Filter by Column Value

To filter a table based on the value in a specific column, you can use the following syntax:

In this example, we are using the Table.SelectRows function to filter the table “Source” based on the value in “Column Name”. The condition argument is defined using the “each” keyword, which tells Power Query to apply the expression to each row in the table. The square brackets are used to specify the column name, and the equals sign is used to compare the value in the column to the desired value.

Filter by Multiple Columns

To filter a table based on the values in multiple columns, you can use the following syntax:

In this example, we are using the Table.SelectRows function to filter the table “Source” based on the values in “Column 1” and “Column 2”. The condition argument is defined using the “each” keyword, and the “and” keyword is used to combine multiple conditions.

Filter by Comparison Operators

To filter a table based on a comparison operator, you can use the following syntax:

In this example, we are using the Table.SelectRows function to filter the table “Source” based on the value in “Column Name” being greater than 10. The condition argument is defined using the “each” keyword, and the “>” operator is used to compare the value in the column to the desired value.

The Table.SelectRows function in Power Query is a powerful tool for filtering rows in a table based on a condition. By understanding the M code behind the function and how to construct the condition argument, you can use the Table.SelectRows function effectively in your data transformation and cleaning tasks.

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

Upcoming Courses

Contact Us