Table.ReplaceMatchingRows

Table.ReplaceMatchingRows is a Power Query M function that replaces specified rows in a table with the provided ones based on the replacement list and optional equationCriteria parameter. The function returns a table with the replaced rows.

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

Syntax

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

Description

Table.ReplaceMatchingRows replaces specified rows with provided rows. It swaps specified rows in table with replacements in replacements ({old, new} format). An optional equationCriteria can control row comparisons.

Examples

Replace the rows [a = 1, b = 2] and [a = 2, b = 3] with [a = -1, b = -2],[a = -2, b = -3] in the table.

Table.ReplaceMatchingRows( 
    Table.FromRecords( {
        [a = 1, b = 2],
        [a = 2, b = 3],
        [a = 3, b = 4],
        [a = 1, b = 2]
    } ),
    {
        {[a = 1, b = 2], [a = -1, b = -2]},
        {[a = 2, b = 3], [a = -2, b = -3]}
    }
 )

 /* Output: 
Table.FromRecords( {
    [a = -1, b = -2],
    [a = -2, b = -3],
    [a = 3, b = 4],
    [a = -1, b = -2]
} )
 */ 

Related functions

Other functions related to Table.ReplaceMatchingRows are:

• Table.ReplaceValue

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