Table.ReplaceValue
Updates the existing value with a new value in designated columns of a table by utilizing the designated replacer function, such as text.Replace or Value.Replace.
Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table
Updates the existing value with a new value in designated columns of a table by utilizing the designated replacer function, such as text.Replace or Value.Replace.
Example 1
Replace the term "goodbye" with "world" in column B, replacing the entire value.
Usage:
Power Query MTable.ReplaceValue(
Table.FromRecords({
[A = 1, B = "hello"],
[A = 2, B = "goodbye"],
[A = 3, B = "goodbyes"]
}),
"goodbye",
"world",
Replacer.ReplaceValue,
{"B"})
Output:
Table.FromRecords({
[A = 1, B = "hello"],
[A = 2, B = "world"],
[A = 3, B = "goodbyes"]
})
Example 2
Replace the term "ur" with "or" in column B, matching any part of the value.
Usage:
Power Query MTable.ReplaceValue(
Table.FromRecords({
[A = 1, B = "hello"],
[A = 2, B = "wurld"]
}),
"ur",
"or",
Replacer.ReplaceText,
{"B"})
Output:
Table.FromRecords({
[A = 1, B = "hello"],
[A = 2, B = "world"]
})
Example 3
Mask the names of US employees.
Usage:
Power Query MTable.ReplaceValue(
Table.FromRecords({
[Name = "Cindy", Country = "US"],
[Name = "Bob", Country = "CA"]
}),
each if [Country] = "US" then [