IF Statement to Calculate Time Difference in Power Query

[Anonymous]

Hi,

I'm trying to calculate the difference between two date/time columns using the following statement in Power Query:

if[#"RTS Date/Time"]=null then Duration.Minutes([#"Out Date/Time"],DateTime.LocalNow()) else Duration.Minutes([#"Out Date/Time"],[#"RTS Date/Time"])

Where:

Out Date/Time = date and time a machine went OUT of service

RTS Date/Time = date and time the machine RETURNED to service

If the machine is still out of service I want the calculation to return the difference between the Out Date/Time and the current date and time.

There are no errors when I write this in Power query but when I let it run I get errors in the column rather than the number of minutes. The error is saying this:

Expression.Error: 2 arguments were passed to a function which expects 1.
Details:
Pattern=
Arguments=[List]

Am I writing my IF statement incorrectly? If so, how do I need to change it to give me the results I want?

[amitchandak · Accepted solution]

@Anonymous , Try like

f[#"RTS Date/Time"]=null then Duration.Minutes([#"Out Date/Time"]- DateTime.LocalNow()) else Duration.Minutes([#"Out Date/Time"]- [#"RTS Date/Time"])

[Anonymous]

@amitchandak It was a s simple as that! Thank you very much. 

Now I have a slightly different problem in that my calculation doesn't seem to allow for time differences over 1 hour. In the screenshot below you can see that I've highlighted two lines where the difference between the Out Date/Time and RTS Date/Time should be over 1 hour but I get less than 1 hour in my new column. Why might that be?

[amitchandak]

@Anonymous , change the position and try

if[#"RTS Date/Time"]=null then Duration.Minutes(DateTime.LocalNow() -[#"Out Date/Time"]) else Duration.Minutes([#"RTS Date/Time"]- [#"Out Date/Time"])

[Anonymous]

@amitchandak Changing the position didn't affect the results but it occurred to me that the Duration.Minutes function might be what's holding me back. So I created a second custom column and simply replaced Duration.Minutes with Duration.Hours. So I now have the hours in one column and minutes in another. I plan to merge these and sort out the formatting to give me the Difference Date/Time column I need. Thanks for your help.