Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    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
subhashree_r's avatar
subhashree_r
Helper I
6 years ago

Hi Anonymous ,

  I'am not able to understand the exactly what that line (Table Type) of the code does to make the corresponding changes.The code that I have used is as given below 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi subhashree_r ,

 

Value.Type returns an object/argument type, in this case, it should be "table", which then is passed to the GroupBy, so it knows beforehand what columns and column types to expect. The third argument in the list for GroupBy is optional and you can remove it to check if there is a problem with this or the one in the function body (it also uses Value.Type and typification in Table.FromColumns, so the problem may be there).

 

Generally speaking, as you run it on the entire table (the second argument in GroupBy = {}), then you should be able to simplify your code to return the resulting table straight from the function. All three lines on the screenshot become (assuming fnAddRunningSum signature and definition is the same as in the original solution):

SumTotalOutput = fnAddRunningSum(#"Added Index")

 

This line (theoretically) will return the same result without the overheads on typification and grouping.

 

Kind regards,

JB