Forum Discussion

thassan77's avatar
thassan77
Frequent Visitor
6 years ago
Solved

Replace value based on Conditions from multiple columns

Hello, I am new to Power Query. I have a table where I want to replace values of a column based on the conditions or values from Multiple columns. I.e. If ColumnA="CVL" or "PVL" and ColumnB= "Retai...
Need Help
  • nandic's avatar
    nandic
    6 years ago

    thassan77 ,
    In Power Query go to option Add Column > Custom Column and add formula like this:
    New Column =
    if [Store Id] = 1 and [Item Id] = 1 then 1 else
    if [Store Id] = 2 and [Item Id] = 2 then 2 else
    if [Store Id] = 3 and [Item Id] = 3 then 3 else 0

    You can have as many rules as you want and you can also and OR statement instead of AND. So everything allowed.
    If you do not have combinations of rules (using multiple columns for one rule) you can choose option Add Column > Conditional Column and easy interface will guide you through the process.

     

  • dax's avatar
    dax
    6 years ago

    Hi thassan77 , 

    You could try below M code 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJARKBSrE60UpOQJa7O5AIB3OdgSw3NyDhCua6AFkeHnDFcL3hcL1gLlBxLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    Custom1 = Table.ReplaceValue(#"Changed Type", each [Column3], each if ([Column1]="A" or [Column1]="B") and [Column2]="TT" then "change" else [Column3], Replacer.ReplaceValue, {"Column3"})
in
    Custom1

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thassan77's avatar
thassan77
Frequent Visitor
6 years ago

Hi Greg

Thanks for the input. Is it possible to create a custom column and replace the value based on the conditions? or Do I need to replace values on the same column while meeting those conditions?

 

thanks.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi thassan77 , 

    You could try below M code 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJARKBSrE60UpOQJa7O5AIB3OdgSw3NyDhCua6AFkeHnDFcL3hcL1gLlBxLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    Custom1 = Table.ReplaceValue(#"Changed Type", each [Column3], each if ([Column1]="A" or [Column1]="B") and [Column2]="TT" then "change" else [Column3], Replacer.ReplaceValue, {"Column3"})
in
    Custom1

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.