cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
subhashree_r Frequent Visitor
Frequent Visitor

Re: How to do a running Sum without group in Power Query?

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.

subhashree_r Frequent Visitor
Frequent Visitor

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

Hey @MarcelBeug ,

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

jborro Member
Member

Re: How to do a running Sum without group in Power Query?

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

jborro Member
Member

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

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.

Highlighted
subhashree_r Frequent Visitor
Frequent Visitor

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

Hi @jborro ,

  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

 

jborro Member
Member

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

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

cyongt_bdf Frequent Visitor
Frequent Visitor

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

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.

vrossouw Member
Member

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

 

 

(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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)