Forum Discussion

djohn05's avatar
djohn05
New Member
4 years ago
Solved

List.Accumulate add multiple conditional columns

Hi,   Now I'm using two steps to create two conditional columns, I need to do that in one step. I need to create col A and col B, examples below.   Separated queries : if xo > 50 then "right" el...
Show and Tell
  • jbwtp's avatar
    jbwtp
    4 years ago

    Hi djohn05,

     

    I think you need to change this part to your new requirements:

    {if _{0}>50 then "right" else "left",if _{1}>50 then "bottom" else "top"}

     

    Or to make wdx223_Daniel vefrsion more generic (assuming you have a variable and large number of columns to add):

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVS0lEyMVSK1YlWMrEEsoECYLYFkK0UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [xo = _t, yo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xo", Int64.Type}, {"yo", Int64.Type}}),
    f = {{"ColA", (x) => if x{0}>50 then 100 else -100, type number}, {"ColB", (x) => if x{0}>50 then 10 else 0, type number}},
    Custom1 = #table(Table.ColumnNames(#"Changed Type")&{"Col A","Col B"},List.Transform(Table.ToRows(#"Changed Type"), (n)=>  n & List.Transform(List.Zip(f){1}, each _(n))))
in
    Custom1

     

    Just pass the list of columns to f.

    Or (I guess closer to your initil idea):

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVS0lEyMVSK1YlWMrEEsoECYLYFkK0UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [xo = _t, yo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xo", Int64.Type}, {"yo", Int64.Type}}),
    f = {{"ColA", (x) => if x[xo]>50 then 100 else -100, type number}, {"ColB", (x) => if x[xo]>50 then 10 else 0, type number}},
    Custom1 = List.Accumulate(f, #"Changed Type", (a, n)=> Table.AddColumn(a, n{0}, (x)=> n{1}(x), n{2}))
in
    Custom1

     

    Same, pass new columns to f.

     

    Kind regards,

    John 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
4 years ago

NewStep=#table(Table.ColumnNames(PreviousStepName)&{"Col A","Col B"},List.Transform(Table.ToRows(PreviousStepName),each _&{if _{0}>50 then "right" else "left",if _{1}>50 then "bottom" else "top"}))

  • djohn05's avatar
    djohn05
    New Member
    4 years ago

    Thanks Daniel it helps me a lot.

     

    Now based on your solution. I want to create two more columns :

     

    col C : if [xo] > 50 then 100 - [xo] else [xo]

    col 😧 if [yo] > 50 then 100 - [yo] else [yo]

     

    xoyocol Ccol D
    52414841
    495249

    48

        
    • jbwtp's avatar
      jbwtp
      Memorable Member
      4 years ago

      Hi djohn05,

       

      I think you need to change this part to your new requirements:

      {if _{0}>50 then "right" else "left",if _{1}>50 then "bottom" else "top"}

       

      Or to make wdx223_Daniel vefrsion more generic (assuming you have a variable and large number of columns to add):

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVS0lEyMVSK1YlWMrEEsoECYLYFkK0UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [xo = _t, yo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xo", Int64.Type}, {"yo", Int64.Type}}),
    f = {{"ColA", (x) => if x{0}>50 then 100 else -100, type number}, {"ColB", (x) => if x{0}>50 then 10 else 0, type number}},
    Custom1 = #table(Table.ColumnNames(#"Changed Type")&{"Col A","Col B"},List.Transform(Table.ToRows(#"Changed Type"), (n)=>  n & List.Transform(List.Zip(f){1}, each _(n))))
in
    Custom1

       

      Just pass the list of columns to f.

      Or (I guess closer to your initil idea):

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVS0lEyMVSK1YlWMrEEsoECYLYFkK0UGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [xo = _t, yo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xo", Int64.Type}, {"yo", Int64.Type}}),
    f = {{"ColA", (x) => if x[xo]>50 then 100 else -100, type number}, {"ColB", (x) => if x[xo]>50 then 10 else 0, type number}},
    Custom1 = List.Accumulate(f, #"Changed Type", (a, n)=> Table.AddColumn(a, n{0}, (x)=> n{1}(x), n{2}))
in
    Custom1

       

      Same, pass new columns to f.

       

      Kind regards,

      John 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi djohn05 ,

       

      Just add two custom column as

      The whole M codes:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVS0lEyMVSK1YlWMrEEsoECsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [xo = _t, yo = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"xo", Int64.Type}, {"yo", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "col C", each if [xo]>50 then 100-[xo] else [xo]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "col D", each if [yo]>50 then 100-[yo] else [yo])
in
    #"Added Custom1"

       

       

       

      Best Regards,

      Stephen Tao

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.