Table.SelectRows Function in Power Query M (5 Easy Examples)

If you want to keep only the rows in a table that meet a condition, and drop the rest, Table.SelectRows is the function you reach for.

You give it a table and a condition, and it returns a new table with just the matching rows. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Table.SelectRows Function

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

where

table (required, table). The table you want to filter.
condition (required, function). A function that tests each row and returns true to keep it or false to drop it. You usually write it with each, for example each [Amount] > 1000.

Returns: a new table containing only the rows where condition returned true. The column structure stays the same. If no row matches, you get an empty table with the same columns.

In plain terms, you hand it a table and a rule, and it gives you back the rows that pass the rule.

Example 1: Keep rows above a number

You have an Invoices query and want to keep only the invoices over 1000.

Here is the starting data:

Invoice	Amount
INV-2001	1450
INV-2002	380
INV-2003	920
INV-2004	75
INV-2005	2100

Filter the rows where Amount is greater than 1000.

let
    Source = Excel.CurrentWorkbook(){[Name="Invoices"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Amount] > 1000)
in
    #"Filtered Rows"

This keeps every row whose Amount is above 1000.

The result keeps only the two large invoices:

Invoice	Amount
INV-2001	1450
INV-2005	2100

The rows at 380, 920, and 75 are dropped because they fail the test.

Example 2: Keep rows that match text

You have an Employees2 query and want only the people in the Sales department.

Here is the starting data:

Name	Department
Aisha	Finance
Marco	Sales
Priya	Finance
Liam	Marketing
Noah	Sales

Filter the rows where Department equals Sales.

let
    Source = Excel.CurrentWorkbook(){[Name="Employees2"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Department] = "Sales")
in
    #"Filtered Rows"

This keeps the rows whose Department is exactly Sales.

The result has only the two Sales rows:

Name	Department
Marco	Sales
Noah	Sales

The text match with = is case-sensitive, so Sales matches but sales would not.

Example 3: Combine two conditions

You have a Deals3 query and want the West deals worth more than 5000.

Here is the starting data:

Rep	Region	Value
Carla	West	6200
Devon	East	3100
Eun	West	1800
Farah	West	9400
Greg	East	7700

Filter the rows where Region is West and Value is over 5000.

let
    Source = Excel.CurrentWorkbook(){[Name="Deals3"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [Region] = "West" and [Value] > 5000)
in
    #"Filtered Rows"

A row is kept only when both parts of the and are true.

The result keeps the two big West deals:

Rep	Region	Value
Carla	West	6200
Farah	West	9400

Eun is in the West but only at 1800, and Greg clears 5000 but sits in the East, so both are dropped.

Example 4: Keep rows whose value is in a list

You have a Tickets4 query and want only the tasks marked High or Urgent.

Here is the starting data:

Task	Priority
T-1	Low
T-2	High
T-3	Medium
T-4	Urgent
T-5	High

Use List.Contains to check each Priority against a list of values to keep.

let
    Source = Excel.CurrentWorkbook(){[Name="Tickets4"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains({"High", "Urgent"}, [Priority]))
in
    #"Filtered Rows"

This keeps a row when its Priority appears anywhere in the {"High", "Urgent"} list.

The result keeps the three high-priority tasks:

Task	Priority
T-2	High
T-4	Urgent
T-5	High

This is cleaner than chaining several or tests when you have more than two values to match.

Example 5: Keep rows whose text contains a word

You have a Products5 query and want only the products with Wireless in the title.

Here is the starting data:

SKU	Title
P-100	Wireless Mouse
P-101	USB Cable
P-102	Wireless Keyboard
P-103	Laptop Stand
P-104	Wireless Charger

Use Text.Contains to test whether each Title includes the word.

let
    Source = Excel.CurrentWorkbook(){[Name="Products5"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Title], "Wireless"))
in
    #"Filtered Rows"

This keeps a row when its Title contains Wireless anywhere in the text.

The result keeps the three wireless products:

SKU	Title
P-100	Wireless Mouse
P-102	Wireless Keyboard
P-104	Wireless Charger

The USB cable and laptop stand are dropped because their titles do not mention Wireless.

Things to keep in mind with Table.SelectRows

Filter dates with the #date literal. Use each [Date] >= #date(2024,1,1) to keep rows from a date onward. Comparing a date column against a text string will not work.
null values do not pass a comparison. A row where [Amount] is null fails each [Amount] > 1000, so it is dropped. Keep blanks on purpose with each [Amount] = null or [Amount] > 1000.
A misspelled column name throws an error. Expression.Error: The column 'Amout' of the table wasn't found. Check the name against the previous step.

Performance and query folding

On a foldable source like SQL Server, Table.SelectRows can fold into a WHERE clause, so the database does the filtering and returns fewer rows.

Simple comparisons fold well, but M-only predicates and custom comparers can break folding, which forces the filter to run locally. Filter as early as possible so later steps work on fewer rows.

Common questions about Table.SelectRows

What is the difference between Table.SelectRows and Table.RemoveRows?

Table.SelectRows keeps rows based on a condition, while Table.RemoveRows drops rows by position using an index and a count, not by a test.

Can the condition use values from more than one column?

Yes. The each function sees the whole row, so you can reference several columns together, as in Example 3 with [Region] and [Value].

List of All Power Query Functions

Related Power Query Functions / Articles: