If you like keeping things neat and understandable in Power BI then you probably like to minimise things to one line. So there are times when, yeah, you could create a new IF column and then delete the original and rename it, but you're probably asking "how can I do this in one line of M code?"
Here's a solution for when you're trying to either remove a certain string(s) within a field, (as well as links to one that shows how to replace them).
Remove part of a string in a field
Say I have a field like this:
My Column
Which platforms do you use? Select all that apply
Tick those which work for you. Select all that apply
Which would you recommend? Select all that apply
Which are value for money? Select all that apply
Select all that apply
I want to get rid of all those uses of "Select all that apply". If I use the standard "replace" tool in Power Query it will blank the fifth line and leave the others untouched. I could create a new column here and then delete the original and rename the new one, but to do it in one move I can use this formula in the Advanced Editor:
#"Replaced Value" = Table.TransformColumns(#"Previous Step Name",{{"My Column", each List.Accumulate({{"Select all that apply","XX"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}}),
Where "XX" is the term you are replacing it with (in this case just put "" i.e. blank).*
It turns out you can also hit several replace terms in one columns at once by adding them into the second set of double curly brackets. So, if I want to replace xx with XX, yy with YY and zz with ZZ in Column1, I can do it like this
#"Replaced Value" = Table.TransformColumns(#"Previous Step Name",{{"Column1", each List.Accumulate({{"xx","XX"},{"yy","YY"},{"zz","ZZ"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}}),
Alternatively if you want to hit multiple fields you can do that too. So, if I want to replace xx with XX in column1, and bb with BB in Column2 I can write:
#"Replaced Value" = Table.TransformColumns(#"Previous Step Name",{
{"Column1", each List.Accumulate({{"xx","XX"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))},
{"Column2", each List.Accumulate({{"bb","BB"}},_,(string,replace) => Text.Replace(string,replace{0},replace{1}))}
}),
And obviously you can do both together if you wish.
*I found this info in the comments to the two videos included in this blog post by Guru G. The blog post itself covers two similar operations, 1. Removing multiple single characters from a field (video) & 2. Replacing a strings within a field with another string (video).
Edit
Here's another simpler way from The Biccountant which is closer to the code you get if you use the standard tool.
= Table.ReplaceValue(#"Previous Step Name", each [Text to remove], "" ,Replacer.ReplaceText,{"Field to remove it from"})
Where the field containing the info you want to remove is called [Text to remove] and the field you are removing it from is called [Field to remove it from].
If you want to replace it with a specific value you just write a word or phrase in text in double quotes.
Alternatively, rather than a blank or text you can replace with the contents of another field, you just add this into the 3rd term like so:
= Table.ReplaceValue(#"Previous Step Name", each [Text to remove], each [Text to replace with], Replacer.ReplaceText,{"Field to remove it from"})
Where [Text to replace with] is the new field you want to replace with.
