Personalized Collum based on the next row value

[areias_br]

Hi,

I have a table with the following colluns:

Date | Hour | OperationCode | ID

Whenever I have a change in a operation, a new line is created with the record of the hour that operation started. 

I need to create a new collum with the "Operation Duration". Being so, it will always be the Hour of the next line - the Hour of current line. 

Any idea on how can i do it in Power Query? 

[ryan_mayu]

could you pls provide some sample data and expected output?

[areias_br]

Sure!

02/08/2024 | 13:50 | Navigation | 1
02/08/2024 | 23:30 | Mooring | 2
03/09/2024 | 01:00 |  Moored | 3

I expect to get the personalized collum as the following

02/08/2024 | 13:50 | Navigation | 1 | 580 minutes
02/08/2024 | 23:30 | Mooring | 2 | 90 minutes
03/09/2024 | 01:00 |  Moored | 3 | ...

[ryan_mayu]

how you get 580 mins and 90 mins?

[areias_br]

02/08/2024 | 13:50 | Navigation | 1 | 580 minutes (23:30 - 13:50 = 09:40 => 09*60 + 40)
02/08/2024 | 23:30 | Mooring | 2 | 15 minutes (23:45 - 23:30 = 15)
02/08/2024 | 23:45 |  Moored | 3 | ...

I've changed the last value because it gets a little bit more complicated when day changes.

[ryan_mayu · Accepted solution]

you can try this

datehour = 'Table'[Date]+'Table'[Hour]

Column =

VAR _next=MINX(FILTER('Table','Table'[datehour]>EARLIER('Table'[datehour])),'Table'[datehour])

var _hour=maxx(FILTER('Table','Table'[datehour]=_next),'Table'[Hour])

return if (ISBLANK(_hour),blank(), if ('Table'[Hour]>_hour, DATEDIFF((date(2020,1,1)+'Table'[Hour]),(date(2020,1,2)+_hour),MINUTE),DATEDIFF((date(2020,1,1)+'Table'[Hour]),(date(2020,1,1)+_hour),MINUTE)))