Downloads
Power Query MTable: rowsIntermediate

Table.RemoveMatchingRows

Removes every row that matches one of the sample records in a list.

Syntax
Table.RemoveMatchingRows(table as table, rows as list, optional equationCriteria as any) as table

Returns: 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

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

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.

Related functions

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