Elapsed Time in Power Query and PowerPivot

[masterelaichi]

Hi,

I wasn't able to find a thread on this topic so I thought I will go ahead and post one

I am trying to calculate the time elapsed between two timestamps in Power Query and Power Pivot. I need to see the result in minutes and extend it to hours if possible

Basically, I have a table which has two columns, Outage Start and Outage End. The values for these columns are of the format dd/mm/yyyy hh:min:sec AM/PM. For instance:

Outage Start: 24/11/2015 7:25:32 AM
Outage End : 24/11/2015 9:25:32 AM

I need to find the time elapsed between these two dates. I tried doing this through Power Query by creating a new custom column and then [Outage End-Outage Start], finally transforming it to a Duraration of type Minutes

The new custom column returns data in the format 00:4:40, for example. However, when I try converting this to minutes I get unexpected results. For example, for a time elapsed of say, 4hours, it doesn't give me the expected result of 240 minutes

Is there a way to do this in Power Query and also Power Pivot?

Thanks

 

[Matt Allington]

You can do it in both. They use the same date/time approach to excel. Each day has a number starting at 1 for 1/1/1900 and then advancing by 1 each day. In power pivot you can simply subtract one date from another and get a date/time. You can format this as decimal and then multiply it be 24 to convert to hours.

In in power query, I think you can use built in functions in the menu to extract the components (date, hour, minute' second) en you can do the maths to find the difference.

 

[masterelaichi]

Thanks for your reply Matt

I think I have figured it out how to do it using Power Query (still have to test it thoroughly), although I am not sure how the Duration button works. What is the difference between Duration->hours, Minutes and Duration-> Total Hours, Total Minutes? They both give me different results

Powerpivot, on the other hand, doesn't seem to be working correctly. I am not sure if I am doing something wrong. When I try subtracting [Outage End]-[Outage Start] I get totally incorrect results. Ex- [24/03/2016 7:38:48 AM] - [24/03/2016 7:15:25 AM] gives me 30/12/1899 12:23:33 AM

Thanks

 

[Matt Allington]

In both cases, you can just subtract the end from the start. In Power Query, first add a custom column and then just do simple subtraction on the 2 columns. The issue you are seeing is that you have the new column formatted as date/time (which is the default for such calculation). Just change it to decimal number and it will give you the decimal days between the 2 dates. Multiply this by 24 to get total hours, 1440 to get total minutes etc.

 

[masterelaichi]

That was easy! Thanks for your help Matt