Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 data looks like, I would like a running sum of the cost in a new column.

Thank you all.

 

2017-10-25 09_02_31-Book1 - Excel.png

 

 

1 ACCEPTED SOLUTION

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
Specializing in Power Query Formula Language (M)

View solution in original post

45 REPLIES 45
Anonymous
Not applicable

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:

query.png

 

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!

Anonymous
Not applicable

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

Worked great! Thanks for your help @cyongt_bdf .

Anonymous
Not applicable

anyone?

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
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

@MarcelBeug Thank you so much for the solution. You are a genius.

 

 

(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

Hi, I am using this query, thank you.

However, it seems as if the total is not per Item.  The total just continue adding?

 

 

bi sum.PNG

Hi to call the function when we say function is accepting two parameters?

Hey @MarcelBeug ,

    I have got an error after applying the cumulative sum as shown below Capture 3.PNG

Anonymous
Not applicable

Hi @subhashree_r ,

 

check what are you passing to Value.Type() Most likely the problem is there. I think if all goes well you should see "table" as an output of this step in both parts of the code.

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 Capture 4.PNG

 

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.

Anonymous
Not applicable

Hi @subhashree_r ,

 

Value.Type returns an object/argument type, in this case, it should be "table", which then is passed to the GroupBy, so it knows beforehand what columns and column types to expect. The third argument in the list for GroupBy is optional and you can remove it to check if there is a problem with this or the one in the function body (it also uses Value.Type and typification in Table.FromColumns, so the problem may be there).

 

Generally speaking, as you run it on the entire table (the second argument in GroupBy = {}), then you should be able to simplify your code to return the resulting table straight from the function. All three lines on the screenshot become (assuming fnAddRunningSum signature and definition is the same as in the original solution):

SumTotalOutput = fnAddRunningSum(#"Added Index")

 

This line (theoretically) will return the same result without the overheads on typification and grouping.

 

Kind regards,

JB

Hey @MarcelBeug ,

  I want to just do the cumulative sum without any grouping action using m query.Can you guide me through the solution for this Problem.

Anonymous
Not applicable

Hi @subhashree_r ,

 

The actual part that does grouping in the original post was where List.Accumulate appears.

This is the function that does the actual TotalSum job and can be used without the GroupBy depends on a scenario you want to implement. There is a way to avoid grouping using custom functions in complex scenarios (where you do not need a SumTotal across the entire table), but it makes the code quite heavy to run and complex to read and understand. Why would you need it?

 

Kind regards,

JB

Hello @MarcelBeug , I tried using the function, what do I change if I want to group it by a different column ? 

 

Thanks !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.