Forum Discussion

Anonymous

Not applicable

3 years ago

Solved

Power Query to calculate based on the previous values

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!

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

2 Replies

Resolver II

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

Anonymous
Not applicable
3 years ago
Excellent ! , Works like a charm. Thanks

Related content