Forum Discussion
How to do a running Sum by group in Power Query?
You can use this query (assuming you want to group on "BU"):
let Source = Table1, TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"}) in #"Expanded AllData"With function fnAddRunningSum:
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Cost],{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + cost})),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
AddedRunningSum
Hi TheOctopusIAm ,
You may try this code where "ColumnHeader" is your column header name:
let
Source = Table1,
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
#"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum(_, "ColumnHeader"), TableType}}),
#"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
#"Expanded AllData"
Thanks for the reply Anonymous. That seems logical, though I'm now getting the following error:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
I've had a search around, but can't find any similar scenarios that generate this same error.
Any thoughts?
Hi TheOctopusIAm ,
I missed the each keyword, please try this code instead:
let Source = Table1, TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", each fnAddRunningSum(_, "ColumnHeader"), TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"}) in #"Expanded AllData"Worked great! Thanks for your help Anonymous .
Hi Anonymous!
I am having trouble getting this to work. I have to apologize, I'm new to M and power queries custom functions.
When I copied the original table submitted by OP, made it into a source and tried to apply the formulas on it chaning "ColumnHeader" to "Costs". However, I received the following error:
Expression.Error: The import AddedRunningSum matches no exports. Did you miss a module reference?How do I solve this? Additionally, is the name of the function "query" important, i.e. does it need to "fnAddRunningSum" or "AddedRunningSum"?
My actual data is regarding COVID-19 from here https://data.europa.eu/euodp/en/data/dataset/covid-19-coronavirus-data/resource/55e8f966-d5c8-438e-85bc-c7a5a26f4863, where I'm trying to apply this running sum per country (e.g. "cases" grouped by "countriesAndTerritories").
Thank you very much!
Hi SegerC ,
I suggest that you keep everything as per the example post until you are learning PBI.
In this case, it says that you are trying to call a function that you do not have (I guess the name of the function can be "fnAddRunningSum" as you created it as per the post). You can either rename it in the place where you call/use it or rename the function in the list of queries from fnAddRunningSum to AddedRunningSum. Either way should work, but the naming should be consistent, so PBI understands what you want.
Kind regards,
JB
