In Excel Power Query, I try to add multiple custom columns with List.Accumulate
and want to fill each column with values from the current
value.
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!