Replacing values in Power BI revisited

Back in 2022 I wrote a post detailing the various ways you can replace part of a string in M code (Power Query). It's actually the post I refer back to the most, but I've recently come across a couple of related posts so thought I would quickly add them here.

Replace values in a column using if statements

I adapted this one from this thread in the Fabric Forum. In the original example I was trying to remove the same string from any appearance of it in a field in a column, but what about if your problem is the other way around – i.e. the part you want to keep is always the same, but the part you want to lose varies, in unknowable ways.

In this case you can use an if function in the middle of the command like this:

= Table.ReplaceValue(#"Previous Step Name", each [Column1],each 

if Text.Start([Column1]), 7) = "My term" then "My new term" else [Column1],

Replacer.ReplaceValue,{"Column1"})

Replace/Swap multiple values for another value at once 

If you want to replace the entire contents of a cell with another value (by specifying the whole contents in both cases) then you can do use this:

= List.Accumulate({{"YYY", "yyyyy"}, {"XXX", "XXXXX"}},

        #"Expanded Table Column", (state, current) => Table.ReplaceValue(state, current{0},  current{1},  Replacer.ReplaceValue, {"Team"}))

Joining/Merging Columns in a single step

As per my previous post sometimes, for tidiness you just want to do something in a single step. So how do you do this with merging / joining / concatenating two columns into one in a single step in Power Query. Easy! (when you know how). Just paste this into your Advanced Editor:

 #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Previous Step Name", {{"Column2", type text}}, "en-GB"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column1"),

Where "Previous Step Name" is the name of the previous step, and "Column1" and "Column 2" are the imaginative titles for the two columns you want to merge. This will leave you with a single column called Column 1.

Remove/Replacing part of a string in Power Query

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.

More of this? I added to the content from this post in a new post November 2024