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

Hey MarcelBeug ,

    I have got an error after applying the cumulative sum as shown below 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi subhashree_r ,

 

check what are you passing to Value.Type() . Most likely the problem is there. I think if all goes well you should see "table" as an output of this step in both parts of the code.

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi subhashree_r ,

       

      You don't have to apply the Table.Group if you don't need that.

      You can simply insert the code of the function directly in the code, instead of calling the function fnAddRunningSum (Assuming #"Added Index" is your last line).

      The Value.Type just construct the data type structure for the Table in the final line.

       

          #"Added Index" = ...,
    TableType = Value.Type(Table.AddColumn(#"Added Index", "Running Sum", each null, type number)),
    Cumulative = List.Accumulate(#"Added Index"[Cost],{},(cumulative,cost) => cumulative & {List.Last(cumulative, 0) + cost}),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(#"Added Index")&{Cumulative},TableType)
in
    AddedRunningSum

       

       

      On the other hand, you may also use List.Sum(List.Range(...)) kind for running sum.