Table.SelectRows Function in Power Query

The Table.SelectRows function in Power Query returns a table containing only the rows that match the given condition.

Syntax

Table.SelectRows(table as table, condition as function) as table

The following are the parameters of the function:
• table: The table we want to filter.
• condition: A function that defines the filtering criteria. The function takes a row (as a record) as input and returns true or false based on whether the row should be included in the result.

Example: Let’s we have a table named MyTable. We want to filter rows where the Score is greater than 30.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Score = 12, Company = "TCS"], 
      [DepartmentID = 1, Name = "Katrina", Score = 20, Company = "TCS"], 
      [DepartmentID = 2, Name = "Ashish", Score = 50, Company = "TCS"], 
      [DepartmentID = 2, Name = "Anjali", Score = 60, Company = "TCS"], 
      [DepartmentID = 1, Name = "Esha", Score = 55, Company = "TCS"], 
      [DepartmentID = 3, Name = "Meenakshi", Score = 40, Company = "TCS"], 
      [DepartmentID = 3, Name = "Esha", Score = 30, Company = "TCS"], 
      [DepartmentID = 2, Name = "Anjali", Score = 25, Company = "TCS"]
    }
  ), 
  return = Table.SelectRows(MyTable, each [Score] > 30)
in
  return

The output of the above code is shown below:

Table.SelectRows function in Power Query

each is a shorthand for creating a function that takes a row as input.
[Score] refers to the Score column in the current row.
The function returns true for rows where Score > 30, and those rows are included in the result.

Advanced Use Cases 1. Multiple Conditions We can combine multiple conditions using logical operators like and, or, and not.

Example: Filter rows where Score > 30 and Name = “Esha”. For not equal to we can use the <> operator.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Score = 12, Company = "TCS"], 
      [DepartmentID = 1, Name = "Katrina", Score = 20, Company = "TCS"], 
      [DepartmentID = 2, Name = "Ashish", Score = 50, Company = "TCS"], 
      [DepartmentID = 2, Name = "Anjali", Score = 60, Company = "TCS"], 
      [DepartmentID = 1, Name = "Esha", Score = 55, Company = "TCS"], 
      [DepartmentID = 3, Name = "Meenakshi", Score = 40, Company = "TCS"], 
      [DepartmentID = 3, Name = "Esha", Score = 30, Company = "TCS"], 
      [DepartmentID = 2, Name = "Anjali", Score = 25, Company = "TCS"]
    }
  ), 
  return = Table.SelectRows(MyTable, each [Score] > 30 and [Name]="Esha")
in
  return

The output of the above code is shown below:

How it works The condition is: Power Query M each [Score] > 30 and [Name]="Esha"
This condition is applied to each row in MyTable. It checks two things:
1. Whether the value in the Score column is greater than 30 ([Score] > 30).
2. Whether the value in the Name column is equal to "Esha" ([Name] = "Esha").
The “and” operator ensures that both conditions results are ANDed, and returns the final result, for a row to be included in the result.

Example: Using or operator. If any of the conditions is true it will include the row in the result.

Power Query M

let
  MyTable = Table.FromRecords(
    {
      [DepartmentID = 1, Name = "Ashish", Score = 12, Company = "TCS"], 
      [DepartmentID = 1, Name = "Katrina", Score = 20, Company = "TCS"], 
      [DepartmentID = 2, Name = "Ashish", Score = 50, Company = "TCS"], 
      [DepartmentID = 2, Name = "Anjali", Score = 60, Company = "TCS"], 
      [DepartmentID = 1, Name = "Esha", Score = 55, Company = "TCS"], 
      [DepartmentID = 3, Name = "Meenakshi", Score = 40, Company = "TCS"], 
      [DepartmentID = 3, Name = "Esha", Score = 30, Company = "TCS"], 
      [DepartmentID = 2, Name = "Anjali", Score = 25, Company = "TCS"]
    }
  ), 
  return = Table.SelectRows(MyTable, each [Score] > 30 or [Name]="Esha")
in
  return

The output of the above code is shown below:

Previous Next