cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PieterN Frequent Visitor
Frequent Visitor

Power Query - Time import

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
1 ACCEPTED SOLUTION

Accepted Solutions
GordS Frequent Visitor
Frequent Visitor

Re: Power Query - Time import

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

Does the time period exceed 24 hrs?

 Capture.JPG

 

5 REPLIES 5
PieterN Frequent Visitor
Frequent Visitor

Re: Power Query - Time import

Image 13.jpg

 

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 Frequent Visitor
Frequent Visitor

Re: Power Query - Time import

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 Frequent Visitor
Frequent Visitor

Re: Power Query - Time import

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 Frequent Visitor
Frequent Visitor

Re: Power Query - Time import

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

Does the time period exceed 24 hrs?

 Capture.JPG

 

PieterN Frequent Visitor
Frequent Visitor

Re: Power Query - Time import

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 104 members 1,554 guests
Please welcome our newest community members: