Adding multiple conditional columns in one step

[Desyn]

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.

[ronrsnfld · Accepted solution]

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"

[Anonymous]

Having to imagine your table structure, you can probably unpivot the date columns, then add an index column starting a 1, make that a text field, then another column named "Subweek" as each [Index]&"a", then do your if...then [Subweek] else [The unpivoted week column name.

Then pivot again.

--Nate