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
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
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
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!