Bulk replace text without custom function or tables

[Anonymous]

I've read a lot of posts about how to perform multiple value replacements in a column without resorting to the inefficient use of multiple single replacement steps which in my case was causing over 15 minutes to apply a change in power query due to millions of rows.

Most of the solutions I found use custom functions, lists or switch tables, all of which work but seemed more complicated than I needed.

I was playing around with M query and the solution below works for me.

I used the GUI to create the first replacement, then extended it out to include other criteria.

The example below uses Replacer.ReplaceValue so will match against the whole cell value.

To match against any portion of a cell value change to Replacer.ReplaceText

The part I like about this is you can also use it for replacement based on a value in a different column to the one you are performing (e.g. replace value in column 1 based on value in column 2 in same row) the replacement on by just changing the column heading after the if statement.

E.g:

#"Replaced Values in Column1" = Table.ReplaceValue(Source, each [Column1],
each if [Column1]="i spelt this uncorrectly" then "This is how to spell it"
else if [Column1]="i really can't type well" then "This is how to type well"
else if [Column1]="i want to use a custom power query function" then "I do not want to use a custom PQ function if I can avoid it!else [Column1], Replacer.ReplaceValue,{"Column1})

[Greg_Deckler]

Nice!