Replace value based on Conditions from multiple columns

[thassan77]

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= "Retailer" then change the value in ColumnC to "Consumer" else reamin as it is. 

Please suggest how to do it or M code for this condition.

Appreciate your help.

[nandic · Accepted solution]

@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.

[Greg_Deckler]

Seems like an if then else with https://docs.microsoft.com/en-us/powerquery-m/text-replace

[nandic · Accepted solution]

@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.

[thassan77]

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 · Accepted solution]

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.