topic Re: Convert excel formula for time conversion in power query in DAX Commands and Tips

Convert excel formula for time conversion in power query

rhaddad87 — Wed, 09 Feb 2022 18:35:56 GMT

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?

Re: Convert excel formula for time conversion in power query

amitchandak — Thu, 10 Feb 2022 05:05:18 GMT

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

Re: Convert excel formula for time conversion in power query

rhaddad87 — Thu, 10 Feb 2022 13:30:14 GMT

Thank you so much, Amit! You are truly a rockstar in this community amitchandak