Power QueryExercises

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
    Complete

Result

OrderIDCustomerRegionProductQtyUnitPriceStatus
A-101Ana RuizNorthKite212.5Complete
A-103Cleo ParkNorthLine43.5Complete

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

OrderIDRegion
A-101North
A-103North
A-104West

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
    NotCancelled

Result

OrderIDStatus
A-1Complete
A-2null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Add [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 = / or condition 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).
Functions in this lesson:Table.SelectRows

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.