Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Replace multiple values in the same column in one step

Hello everybody,   I am looking to a solution to replace several values in a column, in a single step, to have something like this:   for now, here is what my query looks like:  let Sourc...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Here I created a sample for your reference.M code for your reference. 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcslMzyxJzFFwzUstSq9UCClKzCtOTC7JzM9LzFGK1YlW8sjPTVVIzEtRcMksLinKTCoFyYFlfPLLFcLyc0oS01Ox6Av1cwwO9nT3c3WBcoMDXJ093TxB/FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Current Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Current Value", type text}}),
    #"AllReplace" = [#"Digital Energy Transactional" = "BMP", #"Home and Distribution" = "BMP", #"Low Voltage Transactional" = "BMP",#"UNASSIGNED" = "BMP",#"UNSPECIFIED" = "BMP"],
    #"Replaced Value" = Table.TransformColumns(#"Changed Type",{{"Current Value",each Record.FieldOrDefault(AllReplace,_,_)}})
in
    #"Replaced Value"

     

    BTW, Pbix as attached.

     

tackytechtom's avatar
tackytechtom
Most Valuable Professional
2 years ago

Hello y'all,

 

Just wanted to drop off another way on how to solve this namely by utilising a function together with a cleansing table. The idea is to make the whole approach a bit more scalable, by just adding a new rule to the cleansing table. The function would then automatically clean the target table accordng to that new row.

 

Feel free to check this out in the following blog post:
how to replace several characters in a column in just one power query step. (tackytech.blog)

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/