How to do a running Sum by group in Power Query?

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

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!

Hi SegerC 

Sorry for the delay, I had quite an intense week due to COVID impact on the business I am working for.

The error comes up in the Expand step as PBI is lazy and does not calculate anything until it "has to". In this case, the function calls to AddedRunningSum happen on Expand not on Group step.

The "in" bit of each query or function usually refers to the last step in the sequence, not to the name of the query.

Please change you code to this:

(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
    Cumul // not AddedRunningSum!

Everything else in your code seems to be Ok, I think it should work once you fix the function.

If it still complains on TableType, try removing it from the "Table.GroupBy" step and see if the code works.

Kind regards,

John  

@SegerC if you haven't yet managed to make it work, this might help.

This was the final code I used, which worked great. I added a couple of variables for you to make it explicitly clear what you need to change.

You'll need to change three things in Code Sample 1:
1) Source
2) ColumnToGroup
3) ColumnToSum

The result will be your original grouping column, plus a column with all the table type data ready to be expanded.

Code Sample 1: The original table, to which I want to add a grouped running total:

let
    Source = #"SourceDataInput",             //  << - This step can be replaced by a source table or your previous steps.
    ColumnToGroup = "ColumnNameToGroup",     //  << - Change this to the column name that you want to group by.
    ColumnToSum = "ColumnNameToSum",         //  << - Change this to the column that you want a running total of.
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Running Totals" = Table.Group(Source, {ColumnToGroup}, {{"AllData", each fn_AddCol_RunningSum(_,ColumnToSum,ColumnToGroup), TableType}})
in
    #"Grouped Running Totals"

Code Sample 2: Function to add a running total column, using an argument-defined table, SumColumn and GroupColumn:

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

Hi,

I'm new in using PowerBI to make a report and one of the report that I'm making now requires a table that shows running sum by group. I tried following the query script I found here but encountered a 'token eof expected' error that pointed to the "=>" symbol in this part: (MyTable as table, MyColumn as number) =>

Here's the script:

let
    Source = Table.Combine({#"DMU Sched Opening", #"DMU Sched Closing"}),
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"Hours"}, {{"Data", each fn_AddCumul(_,"Data.No  of Tablets per DMU.# Tablets"), TableType}}),
    //type table [Location=text, Opening Day=text, Hours=time, #"No  of Tablets per DMU.# Tablets"=number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Location", "Opening Day", "Hours", "No  of Tablets per DMU.# Tablets","Running Sum"}, {"Data.Location", "Data.Opening Day", "Data.Hours", "Data.No  of Tablets per DMU.# Tablets","Running Sum"})
in
    #"Expanded Data"

(MyTable as table, MyColumn as number) =>
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
Cumul

May I know what's wrong with it? Thank you in advance for the help!

The line where you call the function includes:

each fn_AddCumul(_,"Data.No  of Tablets per DMU.# Tablets"), 

where the second argument is supposed to be a column name.

Is "Data.No  of Tablets per DMU.# Tablets" definitely your column name? At a glance it looks like a combination of a merged column from another table, combined with the name of a previous M step?

Thank you for your reply

I have now changed to the below but I'm still getting the same error 😞

let
    Source = Table.Combine({#"DMU Opening", #"DMU Closing"}),
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    #"Grouped Rows" = Table.Group(Source, {"Hours"}, {{"Data", each Cumul(_,"NumTablets"), type table [Location=text, Opening Day=text, Hours=time, NumTablets=number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Location", "Opening Day", "NumTablets","Running Sum"}, {"Location", "Opening Day", "NumTablets","Running Sum"})
in
    #"Expanded Data"

(MyTable as table, MyColumn as number) =>
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
Cumul