Forum Discussion

Desyn's avatar
Desyn
Regular Visitor
2 years ago
Solved

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: ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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