List.Accumulate add multiple conditional columns

[djohn05]

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

[wdx223_Daniel]

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]

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

[v-stephen-msft]

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.

[jbwtp · Accepted solution]

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