Dynamic range in function

[Anonymous]

Hey,

i need to convert the data to the following formula:
Step = Table.AddColumn(#"Zmieniono typ2", ColsToTransformCPI {0}, each (([2011] - [2010]) / [2010]), Percentage.Type)

I just downloaded raw CPI data from IMF witch has a base year as 2010. I was wondering what would be the best way to scale the following formula through every year (Column Names)(2010-2021) meaning that if next year new data is published the formula would be able to add the next columna utomatically.

I am aware that it is just 10 columns and can be done manually however i am curious as to how can it be done with a dynamic range.

[AlexisOlson]

@ImkeF has an article on a similar problem you can draw some inspiration from:

https://www.thebiccountant.com/2022/02/20/transforming-multiple-columns-at-once-power-query/

The key piece I'm stealing from that is that you can add multiple columns simultaneously by adding a record column with multiple fields and then expanding.

Here's how you can apply that logic along with a list function that calculates the deltas between list items:

let
    Source = Table.FromColumns({{1,2,3}, {20,50,80}, {24,45,84}, {32,90,60}}, {"ID","2010","2011","2012"}),
    #"Changed Type" = Table.TransformColumnTypes(Source,
        {{"ID", Int64.Type}, {"2010", type number}, {"2011", type number}, {"2012", type number}}),
    YearCols = {"2010", "2011", "2012"},
    NewCols = List.Transform(List.Skip(YearCols), each _ & "-Delta"),
    fn_List = (L) => List.RemoveLastN(List.Transform(List.Zip({L, List.Skip(L)}), (a) => (a{1} - a{0}) / a{0}),1),
    #"Added Custom" =
        Table.AddColumn(#"Changed Type", "Deltas",
            (row) => Record.FromList(fn_List(Record.ToList(Record.SelectFields(row, YearCols))), NewCols)
        ),
    #"Expand Deltas" = Table.ExpandRecordColumn(#"Added Custom", "Deltas", NewCols, NewCols),
    #"Percent Type" = Table.TransformColumnTypes(#"Expand Deltas", List.Transform(NewCols, each {_, Percentage.Type}))
in
    #"Percent Type"

[ronrsnfld]

You could probably use List.Generate or List.Accumulate to add the requisite number of calculated columns; with either the Table.AddColumn method or the Table.FromColumns method.

[Anonymous]

The Generate part is ok I have list i can substitue for each year as something like this:
Step2 = Table.AddColumn(#"Zmieniono typ2", ColsToTransformCPI {1},
((ColsToTransform{1} - ColsToTransform{0}) / ColsToTransform{1}), Percentage.Type),
But that obviously doesn't work so the question is how to use list values as column names and then going through the list values in a loop

[ronrsnfld]

I don't see in your posted code where you have used either of the functions I suggested.  With neither that nor a method of reproducing your data input along with knowledge of your desired output, I don't have any more advice.