Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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"
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.
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
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.