none
power query replace multiple values in a column RRS feed

  • Question

  • GoodDay,

    I have a column where it contains same names with spaces in it.  I want to replace with it by removing spaces in it.

    For Eg: MrRichard - Mr Richard, MRPeter - Mr Peter

    Awaiting for your response

    Kind Regards,

    navad

    Wednesday, November 27, 2019 11:25 AM

Answers

  • The formula above removes all spaces in the column. If you need to do this in multiple columns, just list them in the last argument:

    = Table.ReplaceValue(YourTable," ","",Replacer.ReplaceText,{"Column1","Column2","Column3"})

    If you want to do this in all your columns, use this syntax:

    = Table.ReplaceValue(YourTable," ","",Replacer.ReplaceText,Table.ColumnNames(YourTable))
    Wednesday, November 27, 2019 2:33 PM
  • Hello

    did some tests. Only way that I can find is to create a custom function.

    Do demonstrate

    create new function called "ChangeText"

    = (textstring, oldvalue as list, newvalue as list)=>
    let
        Zip = List.Zip({oldvalue, newvalue }),
        TransformText = Text.From(List.Accumulate(Zip, textstring, (state, current) => Text.Replace(state, current{0}, current{1})))
        
        
    in
        TransformText

    create new query called "tblReplace" with 2 column "Old" and "New"

    let Quelle = #table({"Old", "New"}, {{"MrRichard", "Mr Richard"}, {"MrPeter", "Mr Peter"}, {"Peter", "Hans"}}) in

    Quelle

    in the last query a Table.TransformColumns is applied to invoke "ChangeText" with parameter of the column and old - new value of the previous query

    let
        Quelle = #table({"Column"}, {{"MrRichard"}, {"MrPeter"}}),
        TransformColumn = Table.TransformColumns(Quelle , {{"Column", each ChangeText(_, tblReplace[Old], tblReplace[New])}})
    in
        TransformColumn
    Have fun


    Query it

    Wednesday, November 27, 2019 3:25 PM

All replies

  • Hi,

    You may use such function:

    = Table.ReplaceValue(YourTable," ","",Replacer.ReplaceText,{"YourColumn"})
    Wednesday, November 27, 2019 11:58 AM
  • Thanks for the reply, but i have 100's of names in the same way. I want to replace in a bulk
    Wednesday, November 27, 2019 12:17 PM
  • The formula above removes all spaces in the column. If you need to do this in multiple columns, just list them in the last argument:

    = Table.ReplaceValue(YourTable," ","",Replacer.ReplaceText,{"Column1","Column2","Column3"})

    If you want to do this in all your columns, use this syntax:

    = Table.ReplaceValue(YourTable," ","",Replacer.ReplaceText,Table.ColumnNames(YourTable))
    Wednesday, November 27, 2019 2:33 PM
  • Hello

    do you need to replace a complete row of a column or also text parts?

    Jimmy


    Query it

    Wednesday, November 27, 2019 2:36 PM
  • Hello

    did some tests. Only way that I can find is to create a custom function.

    Do demonstrate

    create new function called "ChangeText"

    = (textstring, oldvalue as list, newvalue as list)=>
    let
        Zip = List.Zip({oldvalue, newvalue }),
        TransformText = Text.From(List.Accumulate(Zip, textstring, (state, current) => Text.Replace(state, current{0}, current{1})))
        
        
    in
        TransformText

    create new query called "tblReplace" with 2 column "Old" and "New"

    let Quelle = #table({"Old", "New"}, {{"MrRichard", "Mr Richard"}, {"MrPeter", "Mr Peter"}, {"Peter", "Hans"}}) in

    Quelle

    in the last query a Table.TransformColumns is applied to invoke "ChangeText" with parameter of the column and old - new value of the previous query

    let
        Quelle = #table({"Column"}, {{"MrRichard"}, {"MrPeter"}}),
        TransformColumn = Table.TransformColumns(Quelle , {{"Column", each ChangeText(_, tblReplace[Old], tblReplace[New])}})
    in
        TransformColumn
    Have fun


    Query it

    Wednesday, November 27, 2019 3:25 PM
  • Hi navad32,

    which of the solutions above solved your problem?

    Please mark it/them as answer so that others can benefit from it as well.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, December 15, 2019 7:12 AM
    Moderator