Table.ReplaceMatchingRows Function in Power Query

The Table.ReplaceMatchingRows function in Power Query, replaces specific rows from a table with the new rows.

Syntax

Table.ReplaceMatchingRows(
    table as table, 
    replacements as list, 
    optional equationCriteria as any
) as table

The function has the following parameters:

table: The original table.
replacements: A list of lists, where each inner list contains:
- The row to match
- The replacement row
- Example: { { [1, "A"], [1, "X"] } } means replace [1, "A"] with [1, "X"].
equationCriteria: It is an optional parameter. Tells Power Query how to compare rows (e.g., which columns to match).

Example: Updating Prices. Imagine we have a product table, and we want to replace old prices with new ones.

Power Query M

let
    // Source product table created from records (field names -> less error-prone)
    Products = Table.FromRecords({
        [ProductID = 101, Price = 50],
        [ProductID = 102, Price = 75],
        [ProductID = 103, Price = 100]
    }),

    // Replacements: each item is a pair { oldRecord, newRecord }
    // Here we match the whole row exactly (ProductID + Price) and replace it.
    Replacements = {
        { [ProductID = 101, Price = 50], [ProductID = 101, Price = 55] },  // 101: 50 -> 55
        { [ProductID = 103, Price = 100], [ProductID = 103, Price = 90] }   // 103: 100 -> 90
    },

    // Apply replacements (full-row match)
    Updated = Table.ReplaceMatchingRows(Products, Replacements)
in
    Updated

The output of the above code is shown below:

Table.ReplaceMatchingRows function in Power Query

Previous Next