Power Query - Time import

[PieterN]

Please can you give me some advice. I am still a newbie to the whole Power suite of products and obviously still learning quite a bit. I am using Office 2016 and getting data off our SQL server. The field that i need to work with is describing the time it took to complete a Service call. Eg sample 15 minutes. (the original format on the SQL server is 00:15). When the data is pulled into Power Query, the data is automatically changed to 12:15AM. I am hesitant to change the data format to Text as at the end of the report i will be adding all the times spent together. Does anybody have some advice on how I overcome this? Thank you for any help

[PieterN]

just as an example....

on the 1st row the travel value is 6 minutes and the repair time is 10 minutes. Originally 00:06 and 00:10 respectively. 

However on the highlighted cell, the repair time is 1 hour. Originally 01:00

Thank you

[cikuItreti]

Hi,

my understanding is that PQ is automatically detecting the column type and setting it to "Date/Time". Given that you are interested in displaying the data in those columns as duration you can simply click on the column and from the ribbon or the context menu change the data type to "Duration".

[PieterN]

Thank you for your reply... I have tried that, but the field gives an error on that selection. The only solution that i could find it to convert the field to Text, then extract the 1st and 2nd characters and convert them from Text to Number. Then do the same on the 3rd and 4th characters. Thereafter i did some simple math and calculated the duration as minutes. Not maybe ideal, but i can work with that

[GordS · Accepted solution]

Select the Column > Add Column > Time > Minutes then repeat for Hours.

Does the time period exceed 24 hrs?

 

[PieterN]

It normally would not go past 24 hours, but it is not impossible as some solutions may take more than a couple of days to complete. But thank you this worked