Table.RemoveMatchingRows
Removes every row that matches one of the sample records in a list.
Table.RemoveMatchingRows(table as table, rows as list, optional equationCriteria as any) as tableReturns: A table without the matching rows.
What it does
Table.RemoveMatchingRows deletes rows that match a list of values you give it. Each item in the list is a record, and a row is removed when its values match the fields of that record. It is a clear way to exclude a short list of accounts, vendors or invoice numbers, such as test accounts or known duplicates.
Syntax and arguments
- table: the table to change. Required.
- rows: a list of records. Each record holds the values to match, using column names as field names. Required.
- equationCriteria: optional. It controls how the match is made, for example by naming the columns to compare. Leave it out for a simple match on the fields of each record.
Example
A general ledger contains postings to two test accounts, 9999 and 9998, which must be excluded before the report is built.
let
Source = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
ToExclude = {[Account = "9999"], [Account = "9998"]},
Clean = Table.RemoveMatchingRows(Source, ToExclude)
in
Clean
Every row whose Account is 9999 or 9998 is removed. The other rows pass through unchanged. The account codes are text here, so they are written in quotes.
Things to watch
- The match is exact. A stray space, as in "9999 ", does not match "9999". Use Table.TransformColumns with Text.Trim first.
- A long list written into the query is hard to maintain. For a list that changes, keep it in its own table and join to it, or use Table.Join with an exclusion.
- Make the record fields match the column names exactly, including case.
- Query folding: this step usually does not fold. Check View Native Query if the source is a database.
In business terms
Test accounts, voided invoice numbers, intercompany codes and vendors that are not to be reported are often kept on a short exclusion list. Keeping that list in the query, and in the review notes, makes the exclusions visible and repeatable from one month to the next.