Table.RemoveMatchingRows
TableRemoves all rows from a table that match any row in the given list of records.
Syntax
Table.RemoveMatchingRows(table as table, rows as list, optional equationCriteria as any) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
table | table | Yes | The table to remove rows from. |
rows | list | Yes | A list of records representing the rows to remove. |
equationCriteria | any | No | Optional. Column name(s) or comparer to limit which fields are compared. |
Return Value
table — The table with all rows matching any record in the rows list removed.
Remarks
Table.RemoveMatchingRows removes all rows from the table that match any record in the rows list. Matching uses full record equality by default — every field in each record is compared against the corresponding column. The optional equationCriteria parameter can restrict comparison to specific columns, enabling removal by partial key (e.g., "remove all rows where CustomerName = 'Bob' regardless of other column values").
This function is the inverse of filtering for matches: instead of keeping rows that match, it discards them. It differs from Table.RemoveRows (which removes by position) and from Table.SelectRows (which keeps rows satisfying a predicate). Use Table.RemoveMatchingRows when you have an explicit exclusion list of records.
For large exclusion lists, consider an anti-join via Table.NestedJoin + Table.ExpandTableColumn + filter, which may fold to the data source. Table.RemoveMatchingRows does not fold.
Examples
Example 1: Remove discontinued products from the Products table
let
Products = #table(
{"ProductID", "ProductName", "Category", "Price"},
{{1,"Widget A","Widgets",25.00},{2,"Gadget B","Gadgets",50.00},
{3,"Widget C","Widgets",15.00},{4,"Gadget D","Gadgets",75.00},{5,"Thingamajig E","Misc",120.00}}
),
Discontinued = {[ProductID = 3, ProductName = "Widget C", Category = "Widgets", Price = 15.00],
[ProductID = 5, ProductName = "Thingamajig E", Category = "Misc", Price = 120.00]}
in
Table.RemoveMatchingRows(Products, Discontinued)ProductID | ProductName | Category | Price | |
|---|---|---|---|---|
| 1 | 1 | Widget A | Widgets | 25 |
| 2 | 2 | Gadget B | Gadgets | 50 |
| 3 | 4 | Gadget D | Gadgets | 75 |
Example 2: Remove specific customers by name using equationCriteria
let
Sales = #table(
{"OrderID", "CustomerName", "Product", "Region"},
{{1,"Alice","Widget A","East"},{2,"Bob","Gadget B","West"},
{3,"Charlie","Widget C","East"},{4,"Alice","Gadget D","North"},{5,"Diana","Widget A","West"}}
),
Excluded = {[CustomerName = "Bob"], [CustomerName = "Charlie"]}
in
Table.RemoveMatchingRows(Sales, Excluded, "CustomerName")OrderID | CustomerName | Product | Region | |
|---|---|---|---|---|
| 1 | 1 | Alice | Widget A | East |
| 2 | 4 | Alice | Gadget D | North |
| 3 | 5 | Diana | Widget A | West |
Example 3: Remove multiple specific order IDs from the Sales table
let
Sales = #table(
{"OrderID", "CustomerName", "Product"},
{{1,"Alice","Widget A"},{2,"Bob","Gadget B"},{3,"Charlie","Widget C"},
{4,"Alice","Gadget D"},{5,"Diana","Widget A"}}
),
ToRemove = {[OrderID = 2], [OrderID = 4]}
in
Table.RemoveMatchingRows(Sales, ToRemove, "OrderID")OrderID | CustomerName | Product | |
|---|---|---|---|
| 1 | 1 | Alice | Widget A |
| 2 | 3 | Charlie | Widget C |
| 3 | 5 | Diana | Widget A |