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)The final output — removes all rows from Products that exactly match any record in the Discontinued list, leaving 3 active products.
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")The final output — removes all rows from Sales where CustomerName matches any record in Excluded (comparing only CustomerName), leaving Alice and Diana's orders.
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")The final output — removes all rows from Sales where OrderID matches any record in ToRemove (comparing only OrderID), leaving orders 1, 3, and 5.
OrderID | CustomerName | Product | |
|---|---|---|---|
| 1 | 1 | Alice | Widget A |
| 2 | 3 | Charlie | Widget C |
| 3 | 5 | Diana | Widget A |
Compatibility
