Forum Discussion

rhaddad87's avatar
rhaddad87
Helper I
4 years ago
Solved

Convert excel formula for time conversion in power query

Hi, community!  I have a query where I need to add a custom column to match what my excel spreadsheet does, the formula is:

 

IF(MOD(24*MOD(TEXT([#"Land Time (Military Time)"],"00\:00")-TEXT([#"Take Off Time (Military Time)"],"00\:00"),1),1)<0.6,24*MOD(TEXT([#"Land Time (Military Time)"],"00\:00")-TEXT([#"Take Off Time (Military Time)"],"00\:00"),1),((1440*MOD(TEXT([#"Land Time (Military Time)"],"00\:00")-TEXT([#"Take Off Time (Military Time)"],"00\:00"),1))-1)/60))

 

Is this easy to convert to a Power Query expression?

  • amitchandak's avatar
    amitchandak
    4 years ago

    rhaddad87 , With help from Duration

     

    Duration.Days([Military Time])*24 + Duration.Hours([Military Time])+ Duration.Minutes([Military Time])/60.0 + Duration.Minutes([Military Time])/3600.0

     

    Change formula as per need

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    rhaddad87 , With help from Duration

     

    Duration.Days([Military Time])*24 + Duration.Hours([Military Time])+ Duration.Minutes([Military Time])/60.0 + Duration.Minutes([Military Time])/3600.0

     

    Change formula as per need