Downloads
Power Query MTable: rowsBeginner

Table.SelectRows

Keeps only the rows where a condition is true.

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

Returns: A table with the matching rows, in their original order.

What it does

Table.SelectRows keeps the rows that pass a test and drops the rest. It is the M version of the WHERE clause in SQL, and it is the step you will use most. Use it to keep one account, one month or one customer, or to drop blank lines and subtotal rows from an export.

Syntax and arguments

Example

A controller wants a list of orders that have not yet shipped or been cancelled. The orders table comes from the sample database.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Orders = Source{[Schema = "classicmodels", Item = "orders"]}[Data],
    Open = Table.SelectRows(Orders, each [status] <> "Shipped" and [status] <> "Cancelled"),
    Result = Table.SelectColumns(Open, {"orderNumber", "orderDate", "status"})
in
    Result

The condition runs once for each row. Rows where it returns true are kept and the rest are dropped. Because the test is a simple comparison, Power Query sends it to the database as a WHERE clause, so the server does the filtering and only the kept rows travel to your computer.

Example: a second, different use

A bank export has an Amount column. The reviewer wants every payment of 10,000 or more for a second look.

let
    Source = Excel.CurrentWorkbook(){[Name = "Bank"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"Amount", type number}}),
    Large = Table.SelectRows(Typed, each [Amount] >= 10000)
in
    Large

The type step comes first. If Amount is still text, the comparison is made on text, and "9000" would sort after "10000".

Things to watch

In business terms

Filtering is how finance teams narrow a ledger or a list to one entity, period, customer or threshold before they summarize it. Typical uses are open invoices for an aging report, postings above a review threshold for a control test, and one cost center for a departmental review.

Related functions

All Power Query M functions · View as Markdown · M basics