Forum Discussion

oswin_aria's avatar
oswin_aria
New Member
2 years ago
Solved

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

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

 

Need Help
Tutorial Requests
  • collinq's avatar
    collinq
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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's avatar
    dufoq3
    2 years ago

    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

4 Replies

  • collinq's avatar
    collinq
    Super User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    dufoq3
    Community Champion
    2 years ago

    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