Forum Discussion
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" else "left"
if yo > 50 then "bottom" else "top"
| xo | yo | col A | col B |
| 52 | 41 | right | top |
| 49 | 52 | left | bottom |
Based on List.accumulate and AddColumn how can I create these columns in one step ?
Thanks in advance
Best regards,
Djohn
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 Custom1Just 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 Custom1Same, pass new columns to f.
Kind regards,
John
4 Replies
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"}))
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]
xo yo col C col D 52 41 48 41 49 52 49 48
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 Custom1Just 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 Custom1Same, pass new columns to f.
Kind regards,
John
