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.
Okay so I've accomplished what I wanted here by using List.Accumulate and List.Range in combination with an [Index] column and changeing the List.Accumulate "seed" = to the [Sum] of the first value in the list (2016's inflation thus far).....
= Table.AddColumn(#"Added Index", "Custom", each
List.Accumulate(
List.Range(#"Added Index"[Sum],0,[Index]),[Sum],
(state, current) => current + state))
BUT I still think this should be possbile without having to do an index and a cumulative total using the List.Select function. ImkeF this seems right up your alley?
Full code:
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],
#"Changed Type3" = Table.TransformColumnTypes(Data0,{{"Year", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}, {"Total", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Year"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Total")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Month"}, {"Value", "Inflation Percent"}}),
#"CC: Month #" = Table.AddColumn(#"Renamed Columns", "Month #", each if [Month] = "Jan" then "1" else if [Month] = "Feb" then "2" else if [Month] = "Mar" then "3" else if [Month] = "Apr" then "4" else if [Month] = "May" then "5" else if [Month] = "Jun" then "6" else if [Month] = "Jul" then "7" else if [Month] = "Aug" then "8" else if [Month] = "Sep" then "9" else if [Month] = "Oct" then "10" else if [Month] = "Nov" then "11" else if [Month] = "Dec" then "12" else "Not Possible" ),
#"Changed Type" = Table.TransformColumnTypes(#"CC: Month #",{{"Inflation Percent", type number}, {"Month #", Int64.Type}, {"Year", Int64.Type}}),
#"CC: Fiscal Year" = Table.AddColumn(#"Changed Type", "FY", each if([#"Month #"]=10 or [#"Month #"]= 11 or [#"Month #"] = 12)
then [Year]+1
else if([#"Month #"] <= 9)
then [Year]
else "Not Possible"),
#"Added Custom" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]=10)
then 1
else if([#"Month #"]=11)
then 2
else if([#"Month #"]=12)
then 3
else if([#"Month #"]>=1 and [#"Month #"]<=9)
then [#"Month #"]+3
else "Not Possible"),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"FY", Order.Descending}, {"Fiscal Month", Order.Ascending}}),
#"Grouped Rows2" = Table.Group(#"Sorted Rows", {"FY"}, {{"Sum", each List.Sum([Inflation Percent]), type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows2",{{"FY", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([FY] = 2013 or [FY] = 2014 or [FY] = 2015 or [FY] = 2016)),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 0, 1),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate(List.Range(#"Added Index"[Sum],0,[Index]),[Sum], (state, current) => current + state))
in
#"Added Custom1"
result:
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.