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.

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
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.

 

vivekhn's avatar
vivekhn
Advocate I
4 years ago

Hello,

You can use the if else statements to replace multiple categories in a single power query replace value formula.
For example, If you have a status column with the values A,I,T and wish to replace them with Active, Inactive, and Terminated use the formula provided below.

= Table.ReplaceValue(
#"Filtered Rows", 
each [Status],
each if [Status] = "A" then "Active" else if [Status] = "I" then "Inactive" else "Terminated",
Replacer.ReplaceValue,{"Status"}
)

  • AnnOminous's avatar
    AnnOminous
    Microsoft Employee
    1 year ago

    Great approach, and I was able to extend it so that I could apply a value to one column (column_b) based on the condition/value of another column (column_a). My code looks something like this:

    = Table.ReplaceValue(
    #"Filtered Rows", 
    each [column_b],
    each if [column_a] = "x" or [column_a] = "y" then "new value 1" else if [column_a] = "z" then "new value 2" else [column_b],
    Replacer.ReplaceValue,{[column_b]}
    )

  • AntoineAB1's avatar
    AntoineAB1
    Frequent Visitor
    1 year ago

    Best answer I have found. I adapted it to my problem, and it worked very well:

    Table.ReplaceValue(Source, each [GL_MONTH], each if [GL_MONTH] = "03" then "Mar" else if [GL_MONTH] = "11" then "Nov" else if [GL_MONTH] = "12" then "Dec" else if [GL_MONTH] = "04" then "Apr" else if [GL_MONTH] = "01" then "Jan" else if [GL_MONTH] = "07" then "Jul" else if [GL_MONTH] = "06" then "Jun" else if [GL_MONTH] = "10" then "Oct" else if [GL_MONTH] = "09" then "Sep" else if [GL_MONTH] = "02" then "Feb" else if [GL_MONTH] = "08" then "Aug" else if [GL_MONTH] = "05" then "May" else null,Replacer.ReplaceValue,{"GL_MONTH"})

     

    Thank you!!

    • AnnOminous's avatar
      AnnOminous
      Microsoft Employee
      1 year ago

      Agreed, and I would have marked it as a solution if someone hadn't already marked v-frfei-msft's response as the solution.