Forum Discussion
Adding multiple conditional columns in one step
I am adding 14 conditional columns, which I am doing in 14 steps. This seems like a very clunky way of doing it, and I'd prefer to do it in 1 step if possible.
This is how each step looks like:
Step 1: = Table.AddColumn(#"Replaced Value", "Week 1a", each if [Prospect] <> 0 then [Week 1]*(100/[Prospect]) else [Week 1])
Step 2: = Table.AddColumn(#"Added Conditional Column", "Week 2a", each if [Prospect] <> 0 then [Week 2]*(100/[Prospect]) else [Week 2])
Step 3: = Table.AddColumn(#"Added Conditional Column1", "Week 3a", each if [Prospect] <> 0 then [Week 3]*(100/[Prospect]) else [Week 3])
Step 4 to 14: etc, etc
Any help gratefully received.
You can use the List.Accumulate function.
For example:
let Source = Table.FromRecords({ Record.FromList({500,10,20,30},{"Prospect","Week1","Week2","Week3"}), Record.FromList({0,10,20,30},{"Prospect","Week1","Week2","Week3"})}, type table [Prospect = number,Week1 = number,Week2 = number,Week3 = number]), #"Add Multiple Columns" = List.Accumulate( List.RemoveFirstN(Table.ColumnNames(Source),1), Source, (s,c)=>Table.AddColumn(s,c&"a", each if [Prospect]<>0 then Record.Field(_,c) * 100 / [Prospect] else Record.Field(_,c), type number)) in #"Add Multiple Columns"
2 Replies
You can use the List.Accumulate function.
For example:
let Source = Table.FromRecords({ Record.FromList({500,10,20,30},{"Prospect","Week1","Week2","Week3"}), Record.FromList({0,10,20,30},{"Prospect","Week1","Week2","Week3"})}, type table [Prospect = number,Week1 = number,Week2 = number,Week3 = number]), #"Add Multiple Columns" = List.Accumulate( List.RemoveFirstN(Table.ColumnNames(Source),1), Source, (s,c)=>Table.AddColumn(s,c&"a", each if [Prospect]<>0 then Record.Field(_,c) * 100 / [Prospect] else Record.Field(_,c), type number)) in #"Add Multiple Columns"
