Table.ReplaceMatchingRows
Swaps every row that equals a given row for another row.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.ReplaceMatchingRows(table as table, replacements as list, optional equationCriteria as any) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to change. |
replacements | list | Pairs {old row, new row}, each row a record. |
equationCriteria | any optional | The columns that decide a match, as a name or a list of names. |
Returns
table. The same number of rows, the matching ones replaced.
Remarks
- Every row that matches is replaced, not only the first.
- Give the new row with all of the table's columns.
Examples
A new price for the chairs
Table.ReplaceMatchingRows(
#table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}, {"Chair", 50}}),
{{[Product = "Chair", Price = 50], [Product = "Chair", Price = 55]}})| Product | Price |
|---|---|
| Chair | 55 |
| Desk | 120 |
| Chair | 55 |
Matching on one column
Table.ReplaceMatchingRows(
#table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}}),
{{[Product = "Desk"], [Product = "Desk", Price = 99]}},
"Product")| Product | Price |
|---|---|
| Chair | 50 |
| Desk | 99 |
Related functions
Table.ReplaceValue, Table.RemoveMatchingRows, Table.ReplaceRows.