Forum Discussion
Transform a column with if condition
Hi there,
I'm trying to replace the values of a column based on the value in another column. Works fine if I add an extra column, but I'd like to change the original one.
I have two columns: item sold and quantity sold. I want to divide the value in quantity sold based on the information in item sold. For example: if item sold = x y or z, quantity sold must be divided by 2. If there is another value in item sold, nothing should change.
Is there a way to do this without creating a new column?
Hi Silvermountain,
I wold say it is easier and clearer to add a new column, delete the old one and then rename (you can then hide it behing let..in block to make it look like one step), but if you want to make it fancy, try this one:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxJUdJRMlSK1YGxjcHsnNTiYnSOmVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Action = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Action", type text}, {"Value", Int64.Type}}), Process = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a,n)=> a & {Record.TransformFields(n, {{"Value", each _ + (if n[Action] = "add" then 1 else - 1)}})}), Output = Table.FromRecords(Process, Value.Type(#"Changed Type")) in OutputKind regads,
John
2 Replies
Hi Silvermountain,
I wold say it is easier and clearer to add a new column, delete the old one and then rename (you can then hide it behing let..in block to make it look like one step), but if you want to make it fancy, try this one:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxJUdJRMlSK1YGxjcHsnNTiYnSOmVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Action = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Action", type text}, {"Value", Int64.Type}}), Process = List.Accumulate(Table.ToRecords(#"Changed Type"), {}, (a,n)=> a & {Record.TransformFields(n, {{"Value", each _ + (if n[Action] = "add" then 1 else - 1)}})}), Output = Table.FromRecords(Process, Value.Type(#"Changed Type")) in OutputKind regads,
John
Hi jbwtp ,
Sometimes solutions are indeed way less complex then you'd think. Thanks for the tip to simply remove the old column and rename the new one. It seems best not to try the fancy code you've provided, would in my case probably do more damage then help me reaching my goal 😉
