Forum Discussion

areias_br's avatar
areias_br
Icon for Helper I rankHelper I
1 year ago
Solved

Personalized Collum based on the next row value

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? 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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)))
     
     

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 year ago

    could you pls provide some sample data and expected output?

    • areias_br's avatar
      areias_br
      Icon for Helper I rankHelper I
      1 year ago

      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 | ...