Forum Discussion
IF Statement to Calculate Time Difference in Power Query
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?
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"])
4 Replies
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"])
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?
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"])