Table.SelectRows

Table.SelectRows is a Power Query M function that filters rows in a table based on a specified selection condition. The function returns a table with the rows that match the selection condition.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.SelectRows function filters rows in a table based on a specified condition. It evaluates each row against this condition and returns only those rows where the condition is true.

Examples

Imagine you have a dataset with several columns, and you want to apply a filter. Here’s where Table.SelectRows comes into play.

Returning All Rows

The Table.SelectRows function allows you to filter rows based on a custom function. You can see this function as a condition that needs to be true. The most basic filtering condition could be:

// Returns all rows within the table
Table.SelectRows(Source, each true )

In this example, the function checks each row to see if the condition (each true) is met. Since this condition is always true, all rows from the source table are returned.

Filtering Specific Rows

Now, let’s look at a more practical example. Suppose you want to filter rows where the product description equals “Product B”:

Table.SelectRows(Source, each [Product] = "Product B")

In this formula, we’re telling Power Query to look at each row in the ‘Source’ table and only return those where the ‘Product’ column equals “Product B”. This selective filtering is much more common in real-world scenarios.

The function works on the level of a table row. Since a table row can be seen as a record, [product] refers to the value in the Product field of that row.

Using Custom Functions for Filtering

The following expression is considered identical. But instead of using the shorthand ‘each’ to create a function, it creates a function with x as a parameter.

Table.SelectRows( Source, (x) =>  x[Product] = "Product B" )

The x parameter then refers to a record of the current row’s values. Using this, you could instead filter on all column values by referencing the row without a field:

Table.SelectRows( 
  Source, 
  (x) => x = [ProductKey = 2, Product = "Product B", OrderDate = #date(2024, 2, 20), Sales = 200 ] 
)

/* ________________which is identical to ________________ */

Table.SelectRows( 
  Source, 
  each _ = [ProductKey = 2, Product = "Product B", OrderDate = #date(2024, 2, 20), Sales = 200 ] 
)

Here, we’re filtering for rows that match an entire record, including the product key, product name, order date, and sales amount.

Filtering Beyond Equals

Consider a scenario where you want to filter rows where the ‘ProductKey’ is greater than 3:

Table.SelectRows(Source, each [ProductKey] > 3 )

Simulating the ‘IN’ Operator

As long as the condition in the second argument can return true, you can use it to filter your rows. For instance, Power Query does not have an IN operator. You can, however, use the List.Contains function to filter multiple values in a short statement:

Table.SelectRows(Source, each List.Contains( { 175, 185 }, [Sales] ) )

This example shows how you can filter rows where the ‘Sales’ column contains either 175 or 185.

Try the Code Yourself

To try this out yourself, you can paste the following code in the advanced editor:

let
  Source              = 
  #table(
    type table[ProductKey = Int64.Type, Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
    {
        {1, "Product A", #date(2024, 1, 15), 150 },
        {2, "Product B", #date(2024, 2, 20), 200 },
        {3, "Product C", #date(2024, 3, 10), 175 },
        {4, "Product D", #date(2024, 4, 25), 220 },
        {5, "Product E", #date(2024, 5, 5 ), 185 },
        {6, "Product F", #date(2024, 6, 15), 240 }
    }
  ), 
  filter_Product      = Table.SelectRows ( Source, each [Product] = "Product B" ), 
  each_true           = Table.SelectRows ( Source, each true ), 
  match_entireRow1    = 
    Table.SelectRows (
      Source, 
      ( x ) => x = [ ProductKey = 2, Product = "Product B", OrderDate = #date ( 2024, 2, 20 ), Sales = 200 ] ), 
  match_entireRow2    = 
    Table.SelectRows (
      Source, 
      each _ = [ ProductKey = 2, Product = "Product B", OrderDate = #date ( 2024, 2, 20 ), Sales = 200 ] ), 
  Filter_beyondEquals = Table.SelectRows ( Source, each [ProductKey] > 3 ), 
  IN_operator         = Table.SelectRows ( Source, each List.Contains ( { 175, 185 }, [Sales] ) )
in
  IN_operator

Related functions

Other functions related to Table.SelectRows are:

• Table.FilterWithDataTable
• Table.FindText

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-selectrows