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

Hi SegerC ,

 

I suggest that you keep everything as per the example post until you are learning PBI.

In this case, it says that you are trying to call a function that you do not have (I guess the name of the function can be "fnAddRunningSum" as you created it as per the post). You can either rename it in the place where you call/use it or rename the function in the list of queries from fnAddRunningSum to AddedRunningSum. Either way should work, but the naming should be consistent, so PBI understands what you want.

 

Kind regards,

JB

SegerC's avatar
SegerC
Regular Visitor
6 years ago

Hi Anonymous, thank you for your help!

 

So I'm not 100% sure I understood what you meant but I tried renaming the function so that the name is the same in all the three following places:

1. The list of queries

2. The very end of the function (after in)

3. Where it's called.

It seems to work now, maybe. This is what it looks like after that step:

 

However, after that step, I also run into problems. This is the error I receive from the "Expanded AllData" step:

Expression.Error: We cannot convert a value of type Function to type Table.
Details:
    Value=[Function]
    Type=[Type]

 

In my actual data, I also have a problem with the TabelType parameter, I get this error:

Expression.Error: The name 'TableType' wasn't recognized.  Make sure it's spelled correctly.

running this line:

#"Grouped Rows" = Table.Group(#"Changed Type", {"countriesAndTerritories"}, {{"AllData", each AddedRunningSum(_, "cases"), TableType}})

For reference, here is my script for the example table/query and the function:

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"BU"}, {{"AllData", each AddedRunningSum(_, "Cost"), TableType}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Location", "Month", "Cost", "Running Sum"}, {"Location", "Month", "Cost", "Running Sum"})
in
    #"Expanded AllData"
(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

Thank you for your help!