Table.RemoveMatchingRows - Table Function

Table.RemoveMatchingRows is a Power Query M function that removes all occurrences of specified rows from a table. The function returns a new table with the matching rows removed, and can use an optional equationCriteria parameter for row comparison.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

The Table.RemoveMatchingRows function removes rows from a table that match specified criteria. It compares the values of specified columns in the table with those in a provided list of records, and deletes any matching rows. The function takes three arguments: the table to modify, the rows (as records) to remove, and an optional equationCriteria to define custom matching rules or specify which columns to compare.

Examples

Let’s see some examples of how you can use Table.RemoveMatchingRows in practice.

Removing a Single Row Using All Column Values

Let’s start with a sample dataset called Source, which lists different books and how many times they were borrowed:

Table.First dataset as start in Power Query M

If you want to remove a specific row, such as the second row where the title is To Kill a Mockingbird, you must provide the details of that row in the form of a record (a set of column names and values). Here’s how you can do it:

Table.RemoveMatchingRows( 
  Source, 
  { [ Index = 2, Title = "To Kill a Mockingbird", Genre = "Fiction", TimesBorrowed = 50 ] } 
)

This will remove the row where Index = 2, Title = “To Kill a Mockingbird”, Genre = “Fiction”, and TimesBorrowed = 50. The outcome looks as follows:

Table.RemoveMatchingRows removes row using a record in Power Query M

Removing Multiple Rows by Matching All Values

You can also remove more than one row by specifying multiple records. Let’s say you want to remove both the second and fifth rows:

Table.RemoveMatchingRows( 
  Source, 
  { 
    [ Index = 2, Title = "To Kill a Mockingbird", Genre = "Fiction", TimesBorrowed = 50 ],
    [ Index = 5, Title = "The Hobbit", Genre = "Fantasy", TimesBorrowed = 35 ]
   } 
)

Here we can see the table with two rows removed:

Table.RemoveMatchingRows remove multiple rows in Power Query M

Removing Rows Based on a Single Column with equationCriteria

While providing all column values for every row you want to remove works well, it can be tedious if you’re only concerned with one or two columns. A more flexible approach is to use the third argument of Table.RemoveMatchingRows, called equationCriteria. This allows you to specify the column(s) Power Query should use for row matching.

For example, to remove the rows where the Index is either 2 or 5 (without needing to specify all the other column values), you can do the following:

Table.RemoveMatchingRows( 
  Source, 
  { 
    [ Index = 2 ],
    [ Index = 5 ]
   }, 
   "Index"
)

Here, Power Query only looks at the Index column to determine which rows to remove, making it easier and faster when you’re focusing on specific fields.

Using a Custom equationCriteria to Remove Rows

Sometimes, you may want to define custom conditions to remove rows. For instance, let’s say you want to remove any row where the first digit of the TimesBorrowed value is 3 or 6. You can define a custom equation by transforming the TimesBorrowed column to a text value, extracting the first character, converting it back to a number, and comparing it to 3 or 6.

Table.RemoveMatchingRows( 
  Source, 
  { 
    [ TimesBorrowed = 3 ],
    [ TimesBorrowed = 6 ]
   }, 
   each Number.From( Text.Start( Text.From( [TimesBorrowed] ), 1 ) ) 
)

This expression removes any row where the first digit of TimesBorrowed is either 3 or 6, with the following table as its result:

Table.RemoveMatchingRows equationCriteria argument in Power Query M

Full Power Query M code:

If you want to try these examples for yourself, you can use the following full M code in Power Query’s advanced editor:

let
    Source = Table.FromRecords({
        [ Index = 1, Title = "1984", Genre = "Dystopian", TimesBorrowed = 40 ],
        [ Index = 2, Title = "To Kill a Mockingbird", Genre = "Fiction", TimesBorrowed = 50 ],
        [ Index = 3, Title = "Pride and Prejudice", Genre = "Romance", TimesBorrowed = 20 ],
        [ Index = 4, Title = "The Catcher in the Rye", Genre = "Fiction", TimesBorrowed = 15 ],
        [ Index = 5, Title = "The Hobbit", Genre = "Fantasy", TimesBorrowed = 35 ]
    }),

    // Example 1: Remove a single row
    RemoveRow1 = Table.RemoveMatchingRows(Source, { [ Index = 2, Title = "To Kill a Mockingbird", Genre = "Fiction", TimesBorrowed = 50 ] }),

    // Example 2: Remove multiple rows
    RemoveRow2 = Table.RemoveMatchingRows(Source, { [ Index = 2 ], [ Index = 5 ] }, "Index"),

    // Example 3: Custom condition for removal
    RemoveRow3 = Table.RemoveMatchingRows(Source, { [ TimesBorrowed = 3 ], [ TimesBorrowed = 6 ] }, each Number.From( Text.Start( Text.From( [TimesBorrowed] ), 1 ) ))
in
    RemoveRow3

Learn more about Table.RemoveMatchingRows in the following articles:

Join Types in Power Query
Power Query has different join types for merging queries. Mastering them allows you to easily retrieve new values, keep relevant ones or discard them.. » Read more

Other functions related to Table.RemoveMatchingRows are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-removematchingrows