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.

Syntax

Table.ReplaceValue(table as table, oldValue as any, newValue as any, replacer as function, columnsToSearch as list) as table

About

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.

Explanation

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 [