Forum Discussion
Solved
Sum based on Parameter in Power Query
Hi guys, i'm taking inflation data from statbureau.org/ and attempting to creating a table in PQ that will show me the cumulative inflation % from a given point. For example, if since 2013 inflat...
Yes, there are always multiple ways in M... :-)
let Source = Web.Page(Web.Contents("https://www.statbureau.org/en/united-states/inflation-tables", [Timeout=#duration(0, 0, 1, 0)])), Data0 = Source{0}[Data], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Data0, {"Year"}, "Month", "Inflation Percent"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Year", type number}, {"Inflation Percent", Int64.Type}}), OnlyValidDates = Table.AddColumn(#"Changed Type", "Month #", each Date.Month(Date.From("1-"&[Month]&"-"&Text.From([Year])))), #"CC: Fiscal Month" = Table.RemoveRowsWithErrors(OnlyValidDates, {"Month #"}), #"CC: Fiscal Year" = Table.AddColumn(#"CC: Fiscal Month", "FY", each Number.RoundDown([#"Month #"]/10)+[Year]), #"Added Custom" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]<10) then [#"Month #"]+3 else [#"Month #"]-9), #"Grouped Rows2" = Table.Group(#"Added Custom", {"FY"}, {{"Sum", each List.Sum([Inflation Percent]), type number}}), #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows2", each ([FY] >= 2013)), #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom", (ThisRecord) => List.Sum(Table.SelectRows(#"Filtered Rows1", each [FY]>=ThisRecord[FY])[Sum])) in #"Added Custom2"No need to cater for month numbers outside range 1..12 here, because they would have been removed in the new # "CC: Fiscal Month" step.
Yes, there are always multiple ways in M... :-)
let
Source = Web.Page(Web.Contents("https://www.statbureau.org/en/united-states/inflation-tables", [Timeout=#duration(0, 0, 1, 0)])),
Data0 = Source{0}[Data],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Data0, {"Year"}, "Month", "Inflation Percent"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Year", type number}, {"Inflation Percent", Int64.Type}}),
OnlyValidDates = Table.AddColumn(#"Changed Type", "Month #", each Date.Month(Date.From("1-"&[Month]&"-"&Text.From([Year])))),
#"CC: Fiscal Month" = Table.RemoveRowsWithErrors(OnlyValidDates, {"Month #"}),
#"CC: Fiscal Year" = Table.AddColumn(#"CC: Fiscal Month", "FY", each Number.RoundDown([#"Month #"]/10)+[Year]),
#"Added Custom" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]<10) then [#"Month #"]+3 else [#"Month #"]-9),
#"Grouped Rows2" = Table.Group(#"Added Custom", {"FY"}, {{"Sum", each List.Sum([Inflation Percent]), type number}}),
#"Filtered Rows1" = Table.SelectRows(#"Grouped Rows2", each ([FY] >= 2013)),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom", (ThisRecord) => List.Sum(Table.SelectRows(#"Filtered Rows1", each [FY]>=ThisRecord[FY])[Sum]))
in
#"Added Custom2"No need to cater for month numbers outside range 1..12 here, because they would have been removed in the new # "CC: Fiscal Month" step.
Well that's certainaly more attractive code.
*small correction in #"Changed Type"..... I think you meant to make [Year] Int64.Type and [Inflation Percent] type number...(instead the opposite is in your code)*
Also, thanks for the alternative way of calculating an FY, never thought to do it that way.