Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PieterN
New Member

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

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

Does the time period exceed 24 hrs?

 Capture.JPG

 

View solution in original post

5 REPLIES 5
PieterN
New Member

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

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".

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

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

Does the time period exceed 24 hrs?

 Capture.JPG

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.