List accumulate in groups

Hi Anonymous 

Please see this example file for solution Grouped Running Total in PQ

Using a custom function and List.Generate you can create grouped running totals like this.

Custom Function fxGroupedRunningTotal

(values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    ( 
        ()=> [ GRT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1} 
                 then [GRT = [GRT] + values{[i] + 1}, i = [i] + 1]
                 else [GRT = values{[i] + 1}, i = [i] + 1]
        
             otherwise [i = [i] + 1]
    ,
        each [GRT]
    )
in
    GRTList

This following query loads your data from a table and then calls the function, passing in the ID and Values columns.

let
    Source = Excel.CurrentWorkbook(){[Name="SourceData"]}[Content],
    BufferedValues = List.Buffer(Source[Value]),
    BufferedGroup = List.Buffer(Source[ID]),

    RT = Table.FromColumns(
    {
      Source[ID], Source[Index], Source[Value],
      fxGroupedRunningTotal(BufferedValues, BufferedGroup)
    },
    {
      "ID",
      "Index",
      "Value",
      "Running Total"
    })
in
    RT

Phil

If I answered your question please mark my post as the solution.

If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.