Forum Discussion
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= "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.
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.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 Custom1Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Seems like an if then else with https://docs.microsoft.com/en-us/powerquery-m/text-replace
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.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.
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 Custom1Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
