Compile Running Total in Custom column in Query Editor

[kkanda]

I am working on compiling the cumulative total for FAULT_OPS of equipment over a period of 4 years. The reading is taken monthly and the FAULT_OPS counter is reset. So we have the count for that month only. The table consists of 220 K readings for 6400 Measuring Points. Each Measuring Point corresponds to a specific Euipment No and a Characteristic. The table below shows the readings for two Characteristics for one Equipment No for various months.

CHARACTERISTIC	READ_DATE	READING	MEASURING_POINT
FAULT_OPS	20170104	0	2650209
OTHER	20170104	34	2650210
FAULT_OPS	20170203	1	2650209
OTHER	20170203	23	2650210
FAULT_OPS	20170303	1	2650209
OTHER	20170303	15	2650210

Between each set of the Readings for one Equipment, there are thousands of Readings for other Equipment Nos with different MEASURING_POINT numbers taken during the same month.

We need to create a new Column in Query Editor. Sort the table in Ascending order for the READ_DATE starting from month 201701. Compile a custom function for this new Column which will show the running total of FAULT_OPS for each MEASURING_POINT.

I am at a loss how to include all these conditions in IF.. ELSE statement for the Custom Column.

Please advise me how to go about it and suggest a solution.

Thanks in advance

[v-yiruan-msft]

Hi @kkanda ,

According to the error message, please refer the method in this thread to get the actual error cause. And please try the method in the following links and check whether they can help you get the expected result.

Grouped Running Totals in Power Query

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

In addition, you can create a measure as below by DAX to get it:

Culmulative = 
CALCULATE (
    SUM ( 'Table'[READING] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[MEASURING_POINT] = SELECTEDVALUE ( 'Table'[MEASURING_POINT] )
            && 'Table'[READ_DATE] <= SELECTEDVALUE ( 'Table'[READ_DATE] )
    )
)

Best Regards

[kkanda]

On searching the internet, I found the following Custom function to apply for compiling the running total for each group:-

= (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

I applied this function with the parameters as the source Table as Table, READING as SumColumn and MEASURING_POINT as GroupColumn. I get the error "Evaluation resulted in a stack overflow and cannot continue".

The query runs for some time before the above error appears.

Is this happening because the table has about 220K rows and 6K groups?

[kkanda · Accepted solution]

I searched the internet and the post by Philip Treacy helped me resolve the issue. The reference is:

https://www.myonlinetraininghub.com/grouped-running-totals-in-power-query

It has taken some time for the query to run probably due to the large number of records, but this was done. Thanks to the author for a detailed explanation of the various steps in the query.