Replacing values
In data cleansing, replacing values is one of the most common transformation activities. A simple example is when we have a description column in the source system containing free text, and we want to replace some parts of the description with something else.
Power Query Editor UI uses the Table.ReplaceValue(table as table, OldValue as any, NewValue as any, Replacer as function, columnsToSearch as list) function behind the scenes to replace a value in a table. If we want to replace the value of a List, it uses the List.ReplaceValue(list as list, OldValue as any, NewValue as any, Replacer as function) function. Depending on the value’s data type, the Replacer function can be either Replacer.ReplaceText or Replacer.ReplaceValue. The difference between the two is that we can use Replacer.ReplaceText to replace text values, while we can use Replacer.ReplaceValue to replace any values. For instance, if we want to replace semicolons with colons in a text column...
