Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 data looks like, I would like a running sum of the cost in a new column.
Thank you all.
Solved! Go to Solution.
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 @wgjunsay ,
You may apply different "group by" column in the code (instead of "BU"):
#"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum, TableType}})
Cheers!
Hi people, I tried using this method and it worked, but only when I'm using group by with only one field.
Is there a way to make it work for more than one field? I want to group by name, year and month...
Hi @kotelo ,
Yes, you may group more columns and it still works:
#"Grouped Rows" = Table.Group(Source, {"Col1", "Col2"}, {{"AllData", fnAddRunningSum, TableType}})
Hi MarcelBeug,
Your solution is great!! It helps me a lot to minimize the performance issue based on DAX in the visuals!
I think it is also possible to modify the fnAddRunningSum function slightly to avoid List.Skip:
(MyTable as table) as table => let Source = Table.Buffer(MyTable), TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)), Cumulative = List.Accumulate(Source[Cost],{},(cumulative,cost) => cumulative & {List.Last(cumulative, 0) + cost}), AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType) in AddedRunningSum
Cheers!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |