Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I am struggling with power query for hours to replicate the below logic in the picture. I have diffrent assets with serial numbers that has Total_BAV for the first year. what I need to calculate is the replacemnet amounts based on the Total_BAV which changing each year moving forward with the inflation rates.
below is the expected return values
Any Support is really appreicated!
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
"jdJLDoAgDATQu7gmpvSTeBfCDYg77y/YlYCMJKQNM4u3IKUtbuG5TMxtsFE7daW9PeXgHfaO1HFepQy5eK5fuXpus5zfhugEGjorAwMDA4N0hmNiEGAQYBBg0B8GBQYFBgUG6/6DTgwGDAYMtjLkGw==",
BinaryEncoding.Base64), Compression.Deflate)), type table [#"Sr. No", Year, #"Actual Year", Total_BAV, Inflation_Rates]),
ChangeType = Table.TransformColumnTypes(Source,{{"Sr. No", type text}, {"Year", Int64.Type}, {"Actual Year", Int64.Type}, {"Total_BAV", type number}, {"Inflation_Rates", type number}}),
tcn = Table.ColumnNames(ChangeType) & {"Replacement"},
Group = Table.Group(ChangeType, {"Sr. No"}, {"All", each
let lst = List.Accumulate(List.Skip(_[Inflation_Rates]), {_[Total_BAV]{0}}, (s,c)=> s & {List.Last(s)*(1+c)} )
in Table.FromColumns(Table.ToColumns(_) & {lst}, tcn)
}),
Expand = Table.ExpandTableColumn(Group, "All", List.Skip(tcn))
in
Expand
Excellent ! , Works like a charm. Thanks
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(
"jdJLDoAgDATQu7gmpvSTeBfCDYg77y/YlYCMJKQNM4u3IKUtbuG5TMxtsFE7daW9PeXgHfaO1HFepQy5eK5fuXpus5zfhugEGjorAwMDA4N0hmNiEGAQYBBg0B8GBQYFBgUG6/6DTgwGDAYMtjLkGw==",
BinaryEncoding.Base64), Compression.Deflate)), type table [#"Sr. No", Year, #"Actual Year", Total_BAV, Inflation_Rates]),
ChangeType = Table.TransformColumnTypes(Source,{{"Sr. No", type text}, {"Year", Int64.Type}, {"Actual Year", Int64.Type}, {"Total_BAV", type number}, {"Inflation_Rates", type number}}),
tcn = Table.ColumnNames(ChangeType) & {"Replacement"},
Group = Table.Group(ChangeType, {"Sr. No"}, {"All", each
let lst = List.Accumulate(List.Skip(_[Inflation_Rates]), {_[Total_BAV]{0}}, (s,c)=> s & {List.Last(s)*(1+c)} )
in Table.FromColumns(Table.ToColumns(_) & {lst}, tcn)
}),
Expand = Table.ExpandTableColumn(Group, "All", List.Skip(tcn))
in
Expand
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
39 | |
26 | |
22 | |
21 | |
16 |