Filter rows by value
Write filters that describe the rows you want, so they keep working when the data changes.
~10 min read · beginner · Module 5: Filtering and sorting rows
By Tahir Nas · Examples run in our simulator · Published · Updated
No prerequisites: you can start here.
Filtering looks like deleting rows, but Power Query never deletes anything. It records a rule, Table.SelectRows(table, condition), and reapplies it to whatever data arrives on the next refresh. That's why filters must describe which rows you want, never where they happen to be today.
The condition is a function
The filter arrow writes the condition for you, using each: for every row, [Status] reads that row's Status. Rows where the expression is true are kept. Rows where it is false or null are dropped.
let
Source = #table({"OrderID", "Customer", "Region", "Product", "Qty", "UnitPrice", "Status"}, {
{"A-101", "Ana Ruiz", "North", "Kite", 2, 12.5, "Complete"},
{"A-102", "Ben Okafor", "South", "Reel", 6, 4, "Cancelled"},
{"A-103", "Cleo Park", "North", "Line", 4, 3.5, "Complete"},
{"A-104", "Dev Shah", "West", "Kite", 1, 12.5, "Complete"}
}),
Complete = Table.SelectRows(Source, each [Status] = "Complete" and [Qty] >= 2)
in
CompleteResult
| OrderID | Customer | Region | Product | Qty | UnitPrice | Status |
|---|---|---|---|---|---|---|
| A-101 | Ana Ruiz | North | Kite | 2 | 12.5 | Complete |
| A-103 | Cleo Park | North | Line | 4 | 3.5 | Complete |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Combining conditions
Use and when every condition must hold, or when any may. Brackets make the intent explicit: ([Region] = "North" or [Region] = "West") and [Status] = "Complete". For a longer list of allowed values, List.Contains reads better than a chain of or:
let
Source = #table({"OrderID", "Customer", "Region", "Product", "Qty", "UnitPrice", "Status"}, {
{"A-101", "Ana Ruiz", "North", "Kite", 2, 12.5, "Complete"},
{"A-102", "Ben Okafor", "South", "Reel", 6, 4, "Cancelled"},
{"A-103", "Cleo Park", "North", "Line", 4, 3.5, "Complete"},
{"A-104", "Dev Shah", "West", "Kite", 1, 12.5, "Complete"}
}),
Regions = Table.SelectRows(Source, each List.Contains({"North", "West"}, [Region]))
in
Table.SelectColumns(Regions, {"OrderID", "Region"})Result
| OrderID | Region |
|---|---|
| A-101 | North |
| A-103 | North |
| A-104 | West |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
null in conditions
Comparing with null gives null: null >= 4 is null, not false. Table.SelectRows treats null as not-true, so rows with a missing Qty fall out of [Qty] >= 4 automatically. With <> it's different: null <> "Cancelled" is true, so a null Status survives an exclusion filter:
let
Source = #table({"OrderID", "Status"}, {{"A-1", "Complete"}, {"A-2", null}, {"A-3", "Cancelled"}}),
NotCancelled = Table.SelectRows(Source, each [Status] <> "Cancelled")
in
NotCancelledResult
| OrderID | Status |
|---|---|
| A-1 | Complete |
| A-2 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
[Status] <> null and in front when rows without a status should go too.Where to find this in Excel
- Excel for Windows
- Column header filter arrow > Text Filters, Number Filters or Date Filters
- What you see
- The filter menu lists the column's values and type-specific rules; the Filter Rows dialog has Basic and Advanced modes.
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).
Common mistakes
- Removing rows 3 to 5 because those were the bad ones today, instead of describing them by value.
- Keeping the first N rows when you meant 'the rows from this month'.
- Filtering on a value with a trailing space that the next export won't have, or a different capitalisation.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: the column filter arrow offers value checkboxes and Text/Number/Date Filters; both write Table.SelectRows.
- Ticking values in the checkbox list writes an
=/orcondition with today's values. For 'everything except X', use Does Not Equal so new values pass through. - The simulator follows M's rules: rows whose condition evaluates to null are dropped, and text comparison is ordinal (case-sensitive).
Next step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.
Practise: Keep only completed orders (kite shop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- beginner~5 minFree
Keep only completed orders (kite shop)
Filter out cancelled and pending orders so only completed sales remain.
- beginner~5 minPro
Keep only completed orders (bakery)
Filter out cancelled and pending orders so only completed sales remain.
- beginner~5 minPro
Keep only completed orders (bike rental)
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 minPro
Find bulk orders in a price band (bakery)
Keep orders of at least 4 units priced under 20, ignoring rows with no quantity.
- beginner~7 minPro
Find bulk orders in a price band (bike rental)
Keep orders of at least 4 units priced under 20, ignoring rows with no quantity.
- 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.
- intermediate~7 minPro
Keep orders for a list of products (bakery)
Keep only orders whose Product is in the promotion list held in a separate PromoProducts table.
- intermediate~7 minPro
Keep orders for a list of products (bike rental)
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.
- beginner~6 minPro
Find stock held in warehouse B and running low (pharmacy)
Keep rows in warehouse WH-B where OnHand is below the reorder level.
- beginner~6 minPro
Find stock held in warehouse B and running low (cafe)
Keep rows in warehouse WH-B where OnHand is below the reorder level.
- intermediate~7 minFree
Remove completely blank rows, keep partly empty ones (clinic)
A sign-in sheet has spacer rows where every cell is empty. Remove those, but keep real rows that just lack a phone or city.
- intermediate~7 minPro
Remove completely blank rows, keep partly empty ones (workshop)
A sign-in sheet has spacer rows where every cell is empty. Remove those, but keep real rows that just lack a phone or city.
- intermediate~8 minFree
Filter orders to a date window from a parameter table (kite shop)
A one-row Settings table holds StartDate and EndDate. Keep orders inside that window, both ends included.
- intermediate~8 minPro
Filter orders to a date window from a parameter table (bakery)
A one-row Settings table holds StartDate and EndDate. Keep orders inside that window, both ends included.
- intermediate~8 minPro
Filter orders to a date window from a parameter table (bike rental)
A one-row Settings table holds StartDate and EndDate. Keep orders inside that window, both ends included.
- intermediate~8 minFree
Filter with and/or conditions in the right grouping (kite shop)
Keep big orders from anywhere, plus non-cancelled orders from North or South. Parentheses matter.
- intermediate~8 minPro
Filter with and/or conditions in the right grouping (bakery)
Keep big orders from anywhere, plus non-cancelled orders from North or South. Parentheses matter.
- intermediate~8 minPro
Filter with and/or conditions in the right grouping (bike rental)
Keep big orders from anywhere, plus non-cancelled orders from North or South. Parentheses matter.
- intermediate~14 minPro
Project: build the weekly reorder list (hardware)
List the items below their reorder level with how many to order and the supplier's email, sorted for the buyer.
- intermediate~14 minPro
Project: build the weekly reorder list (pharmacy)
List the items below their reorder level with how many to order and the supplier's email, sorted for the buyer.
- intermediate~14 minPro
Project: build the weekly reorder list (cafe)
List the items below their reorder level with how many to order and the supplier's email, sorted for the buyer.