Sum the hours in next few rows if same group

[rovers]

Please advise how to solve this problem in power query. I have a data set consisting: Unique Order IDs, Operation Number, Work Center, Hours. The data is sorted by Order ID, then operation number in ascending order. For example,

Order ID, Op Number, Work Center, Hours

111, 10, AA, 1.1

111, 20, AA, 1.2

111, 30, AA, 1.3

111, 40, BB, 1.4

111, 50, BB, 1.5

111, 60, AA, 1.6

111, 70, AA, 1.7

222, 10, AA, 1.8

222, 20, BB, 1.9

I want a result table that sums up the hours when the next Operation is for the same Work Center:

111, 10, AA, 3.6  (=1.1+1.2+1.3)

111, 40, BB, 2.9 (=1.4+1.5)

111, 60, AA, 3.3 (=1.6+1.7)

222, 10, AA, 1.8

222, 20, BB, 1.9

Thank you.

[ryan_mayu]

@rovers 

here is a workaround for you. pls see the attachment below

[rovers]

Great, nicely done. Instead of having the table as a visual, is it possible to have the results in a new data table instead?

[ryan_mayu · Accepted solution]

@rovers 

then you just need an index column and use DAX to create a new table

Table 2 = 
VAR tbl=ADDCOLUMNS('Table',"check",var _lastorder=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[Order ID])
var _lastcenter=maxx(FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1),'Table'[ Work Center])
VAR _check=if(_lastorder<>'Table'[Order ID]||_lastcenter<>'Table'[ Work Center],"Yes") 
return _check)
var tbl2=ADDCOLUMNS(tbl,"opnumber",if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number])))
return SUMMARIZE(tbl2,'Table'[Order ID],[opnumber],'Table'[ Work Center],"hours",sum('Table'[ Hours]))

pls see the attachment below

[rovers]

Thanks for the solution. However, my dataset has 200,000+ rows and the application hangs while computing Column 2. Is there a more optimal solution? In additional, do you have a solution in Power Query?

[ryan_mayu]

@rovers 

i don't know how to do this in PQ. Since you have accepted the solution, i suggest you to post a new subject that will have a new solution quickly.

[rovers]

I managed to overcome the issue by modifying Column 2, by adding another AND condition to filter the search to a smaller list of same Order ID:

From

if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number]

To

if([check]="Yes",'Table'[ Op Number],maxx(FILTER(tbl,([check])="Yes"&&'Table'[Order ID]=EARLIER('Table'[Order ID])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[ Op Number]

[ryan_mayu]

glad to hear that.