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
TheOctopusIAm's avatar
TheOctopusIAm
Frequent Visitor
6 years ago

This is great, though how should I call this function now that it has two arguments?

78chris 

TheOctopusIAm's avatar
TheOctopusIAm
Frequent Visitor
6 years ago

 

78chris 

Thanks Chris, you posted this code to allow me to select the specific column :

 

(MyTable as table, MyColumn as text) =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "Cumul", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Table.Column(Source,MyColumn),{0},(cumulative,MyColumn) => cumulative & {List.Last(cumulative) + MyColumn})),
    Cumul = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningSum

 

Below is the original code, which calls this function. What changes need to be made to this to call the function, now that it has two arguments?

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"
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi TheOctopusIAm ,

     

    You may try this code where "ColumnHeader" is your column header name:

    let
    Source = Table1,
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", fnAddRunningSum(_, "ColumnHeader"), TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
    #"Expanded AllData"
    • TheOctopusIAm's avatar
      TheOctopusIAm
      Frequent Visitor
      6 years ago

      Thanks for the reply Anonymous. That seems logical, though I'm now getting the following error:

       

      Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

       

       

      I've had a search around, but can't find any similar scenarios that generate this same error.

      Any thoughts? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi TheOctopusIAm ,

         

        I missed the each keyword, please try this code instead:

        let
    Source = Table1,
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", each fnAddRunningSum(_, "ColumnHeader"), TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
    #"Expanded AllData"