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 ,

 

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.