Table.SelectRows
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Keeps the rows for which a condition is true.
When to use it
Use it whenever rows should be kept or dropped by a rule: valid statuses, a date range, non-blank keys. The rule runs on every refresh, so new rows are filtered the same way. To keep rows whose value is in a list, combine it with List.Contains rather than chaining or conditions.
Syntax
Table.SelectRows(table as table, condition as function) as tableParameters
- table
- The table to filter.
- condition
- A function of the row, usually written with each, returning true to keep the row.
Returns
A table with the same columns and only the matching rows.
Example
Table.SelectRows(#table({"Region", "Product", "Qty"}, {{"North", "Kite", 2}, {"South", "Reel", 5}, {"North", "Reel", 1}, {"West", "Kite", 4}}), each [Qty] >= 2)Result
| Region | Product | Qty |
|---|---|---|
| North | Kite | 2 |
| South | Reel | 5 |
| West | Kite | 4 |
Example: keep rows for several values
List.Contains turns a list of allowed values into one condition, which stays readable as the list grows.
Table.SelectRows(#table({"Region", "Rep", "Amount"}, {{"North", "Ana", 120}, {"South", "Bo", 80}, {"North", "Cy", 200}, {"South", "Ana", 50}}), each List.Contains({"Ana", "Cy"}, [Rep]))Result
| Region | Rep | Amount |
|---|---|---|
| North | Ana | 120 |
| North | Cy | 200 |
| South | Ana | 50 |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Rows where the condition is null are dropped, so comparisons with null exclude the row.
- Text comparisons are case-sensitive.
Common mistakes
- Forgetting each: Table.SelectRows(Source, [Qty] > 0) fails because [Qty] needs a row.
- Filtering on text before trimming it, so "North " slips past a test for "North".
- Expecting null to count as false and pass a <> test: a comparison with null is null, and the row is dropped.
The matching command in Excel
- Excel for Windows
- Column header filter arrow > Text Filters, Number Filters or Date Filters
- What you see
- Each filter you apply in the menu or the Filter Rows dialog is recorded as a filtering step.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Filter by values in a column (Microsoft Learn).
Exercises that use Table.SelectRows
- beginner~10 minFree
Clean and summarize a sales export
Trim messy region names, keep valid completed orders, remove an exact duplicate and total revenue by region.
- beginner~5 minFree
Keep only completed orders (kite shop)
Filter out cancelled and pending orders so only completed sales remain.
- beginner~7 minFree
Find bulk orders in a price band (kite shop)
Keep orders of at least 4 units priced under 20, ignoring rows with no quantity.
- beginner~7 minFree
Fix a query whose steps point at the wrong step (kite shop)
A step was renamed and the next step still refers to the old name. Repair the chain so the query runs.
- intermediate~7 minFree
Keep orders for a list of products (kite shop)
Keep only orders whose Product is in the promotion list held in a separate PromoProducts table.
- beginner~6 minFree
Find stock held in warehouse B and running low (hardware)
Keep rows in warehouse WH-B where OnHand is below the reorder level.
Learn more
- Filter rows by value lesson
- How to open the Power Query editor in Excel guide
- Your first Power Query in Excel, from table to refresh guide
Related functions
Official documentation: Table.SelectRows on Microsoft Learn.