Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.