Power Query: Add multiple custom columns with List.Accumulate

Question

In Excel Power Query, I try to add multiple custom columns with List.Accumulateand want to fill each column with values from the currentvalue.

Here is my example code:

let
    Query1 = let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"Region", "Industry", "Product type", "Series", "2000", "2001", "2002", "2003", "2004", "2005"}, {"Region", "Industry", "Product type", "Series", "2000", "2001", "2002", "2003", "2004", "2005"})
in
    #"Expanded Content",
    #"Changed Type" = Table.TransformColumnTypes(Query1,{{"2000", type number}, {"2001", type number}, {"2002", type number}, {"2003", type number}, {"2004", type number}, {"2005", type number}}),
    #"Added Custom" = List.Accumulate({2000..2005}, #"Changed Type", (state, current) => Table.AddColumn(state, "P_" & Number.ToText(current), each [current]))
in
    #"Added Custom"

Everything works fine and I get 6 new columns with the names P_2000, P_2001, etc. As an example I wanted to fill each new column with the respective data from the "old columns". So in column "P_2000" I would like to have the same numbers as in the existing column "2000".

The error comes up with each [current] because it does not recognize the column name. Is there any workaround? I tried to convert the "current" value into text, but this did also not work for me.

Happy for any ideas!

chillin chillin · Accepted Answer · 2020-08-09T12:09:03

Untested, but I think you'll want to change this line:

#"Added Custom" = List.Accumulate({2000..2005}, #"Changed Type", (state, current) => Table.AddColumn(state, "P_" & Number.ToText(current), each [current]))

to:

#"Added Custom" = List.Accumulate({2000..2005}, #"Changed Type", (state, current) => Table.AddColumn(state, "P_" & Number.ToText(current), each Record.Field(_, current)))

When handling records and accessing a field, I believe whatever you put between [ and ] is treated literally. Briefly and informally, the difference is:

[current] = get the field that is literally named "current"
Record.Field(_, [current]) = get the field that is named whatever current's value is

Unrelated, but since the columns you're adding are identical to the columns being copied, it might make more sense to use Table.DuplicateColumn (details) instead of Table.AddColumn (details) inside your List.Accumulate code.

Power Query: Add multiple custom columns with List.Accumulate

1 Answers