Forum Discussion

Silvermountain's avatar
Silvermountain
Frequent Visitor
4 years ago
Solved

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: ite...
Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    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
    Output

     

    Kind regads,

    John

     

jbwtp's avatar
jbwtp
Memorable Member
4 years ago

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
    Output

 

Kind regads,

John

 

Silvermountain's avatar
Silvermountain
Frequent Visitor
4 years ago

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 😉