Replace Multiple Values (Character) Inside a Value (Column)

[oswin_aria]

Hello there..

I just want to do simple thing: to replace number 1, 2, 3 (only this three number, not all number) with letter "X" using Power Query..
Like this one:

But instead using Replace Value three times like this:

Is there another more simple way?

I've tried using formula with array:

• = Table.ReplaceValue(#"Replaced Value",("1","2","3"),"X",Replacer.ReplaceText,{"Coba"})
• = Table.ReplaceValue(#"Replaced Value",{"1","2","3"},"X",Replacer.ReplaceText,{"Coba"})

But none of them works..

Thank you

[collinq · Accepted solution]

Hi @oswin_aria ,

As always, in Power Query, there are many different ways to do the same thing.  So, my way might not be the absolute best way but it works for me!  😁

I had this as my test data:

And I was able to convert it to this:

In one replace rather than three.  Here is the M code that I used:
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Replace123 = (text as text) as text => Text.Replace(Text.Replace(Text.Replace(text, "1", "x"), "2", "x"), "3", "x"),
#"Replaced Values" = Table.TransformColumns(#"Changed Type",{{"Column1", each Replace123(_), type text}})
in
#"Replaced Values"

[Anonymous · Accepted solution]

Hi @oswin_aria ,

Provide another idea:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQydnRyVorVATIdjZyMoUxDR0cjIycnY2NnoEAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Coba = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Coba", type text}}),
    CharsToReplace = {"1", "2", "3"},
    ReplaceWith = "X",
    ReplacedText = Table.TransformColumns(#"Changed Type",{{"Coba", each List.Accumulate(CharsToReplace, _, (state, current) => Text.Replace(state, current, ReplaceWith))}})
in
    ReplacedText

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[dufoq3 · Accepted solution]

Hi @oswin_aria, another solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQydnRyVorVATIdjZyMoUxDR0cjIycnY2NnoEAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Coba = _t]),
    AddedCustom = Table.AddColumn(Source, "Custom", each Text.Combine(List.ReplaceMatchingItems(Text.ToList([Coba]), {{"1", "X"}, {"2", "X"}, {"3", "X"}})), type text)
in
    AddedCustom

[Anonymous · Accepted solution]

Hi @oswin_aria ,

Provide another idea:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQydnRyVorVATIdjZyMoUxDR0cjIycnY2NnoEAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Coba = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Coba", type text}}),
    CharsToReplace = {"1", "2", "3"},
    ReplaceWith = "X",
    ReplacedText = Table.TransformColumns(#"Changed Type",{{"Coba", each List.Accumulate(CharsToReplace, _, (state, current) => Text.Replace(state, current, ReplaceWith))}})
in
    ReplacedText

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[collinq · Accepted solution]

Hi @oswin_aria ,

As always, in Power Query, there are many different ways to do the same thing.  So, my way might not be the absolute best way but it works for me!  😁

I had this as my test data:

And I was able to convert it to this:

In one replace rather than three.  Here is the M code that I used:
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Replace123 = (text as text) as text => Text.Replace(Text.Replace(Text.Replace(text, "1", "x"), "2", "x"), "3", "x"),
#"Replaced Values" = Table.TransformColumns(#"Changed Type",{{"Column1", each Replace123(_), type text}})
in
#"Replaced Values"

[oswin_aria]

It works like magic! Thanks a lot...