Table.SelectRows
Keeps only the rows where a condition is true.
Table.SelectRows(table as table, condition as function) as tableReturns: 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
- table: the table to filter. In a query this is usually the previous step. Required.
- condition: a function that returns true for each row you want to keep. Write it with
each, for exampleeach [Status] = "Open". Insideeach, a name in square brackets is a column of the current row. Required.
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
- Filter on typed columns. A number stored as text compares as text, which gives wrong results for ranges.
- A blank value does not pass a test such as
>= 10000, so blank rows drop out. To find blanks, test= null. - Query folding: simple comparisons on database columns fold into a WHERE clause. A condition that calls a custom function usually does not fold. Check View Native Query on the step.
- Filter early. Fewer rows make every later step faster.
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.