Forum Discussion
Anonymous
8 years agoNot applicable
How to do a running Sum by group in Power Query?
Hi Everyone, I am trying to do a running sum by group in Power Query (m language). Thank you. All solutions I found was to use DAX which I cannot use for my data at this time. Here is what my ...
- 8 years ago
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
Jcarofi
4 years agoFrequent Visitor
Please and if I want the accumulated taking into account the BU and Location columns- Anonymous4 years agoNot applicable
These lines:
#"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"})would look like:
#"Grouped Rows" = Table.Group(Source, {"BU", "Location"}, {{"AllData", fnAddRunningSum, TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Month", "Cost", "Running Sum"}, {"Month", "Cost", "Running Sum"})- Jcarofi4 years agoFrequent Visitor
It doesn't work, I already tried with a similar data I need to calculate the column accumulated by 2 arguments.- Anonymous4 years agoNot applicable
Ok.
This is a modified fnAddRunningSum - nothing really important, just made the values column name a variable to untie the internal data structure to the main table from the computation logic in the function:
(MyTable as table, values as text) as table => let Source = Table.Buffer(MyTable), TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), Cumulative = List.Skip(List.Accumulate(Table.Column(Source, values),{0},(cumulative,cost) => cumulative & {List.Last(cumulative) + Number.From(cost)})), AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) in AddedRunningSumThis is the main table and the call to the function above using your sample data:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKlTSUUoEYkMDpVgdBBeVZ4xPEsJLxsozwSdpik0yBdVKGNcMlYuNB3JPEgbPBJVrhE1tMqraZKyS5hiSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Est = _t, CICL = _t, ve = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Est", type text}, {"CICL", type text}, {"ve", type number}}), TableType = Value.Type(Table.AddColumn(#"Changed Type", "Running Sum", each null, type number)), #"Grouped Rows" = Table.Group(Source, {"Est", "CICL"}, {{"AllData", each fnAddRunningSum (_, "ve"), TableType}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"ve", "Running Sum"}, {"ve", "Running Sum"}) in #"Expanded AllData"Please note that the function call has changed slightly as we added the second parameter (value column name) to the function which calculates running totals.
This is the output of the main table/query:
EstCICLveRunning Sum
q a 10 10 q a 0 10 q a 30 40 q a 0 40 q a 0 40 c a 0 0 c a 40 40 c a 0 40 c a 50 90 c a 0 90 d a 30 30 d a 60 90 d a 0 90 d a 0 90 q b 0 0 q b 40 40 q b 20 60 q b 0 60 c b 40 40 c b 0 40 c b 70 110 c b 0 110 Please let me know if you have any questions.
Kind regards,
JB