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.

     

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]}
)