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

Duration time MySQL

Hi all,

I have in a MySQL DB a stored procedure returning for each key a column of duration in hours that is of  type TIME. 

Importing in Power BI Desktop I get correct values unless values are less than 23:59:59 (otherwise is the remainder of division by 24) because Power BI recognize it as time of the day and therefore for example 25 hours become 01:00:00. Applying in Power BI a conversion to duration gives errors. 

Which type I can return in the procedure and/or Power BI settings I have to use?

1 ACCEPTED SOLUTION

Accepted Solutions
Gravanita Regular Visitor
Regular Visitor

Re: Duration time MySQL

@v-frfei-msft

I went back to my answer to check your suggestion and I didn't manage to make it work. In the process, I created a text file with sample data. It seems that what I also suggested doesn't work when data is not coming in from excel.

duration text 2.JPGduration text 3.jpg

 

Checked the documentation on converting Duration from text Duration.FromText and it seems that it hh can't be greater than 23.

error 24.JPGhh can't be greater than 23.

Capture.JPGLargest duration expected

 

To represent duration with days it has to be in the format [-]ddd.hh:mm[:ss].

 

 

 

To answer the initial question, how to get the data inside powerbi as a duration, you can transform it  using the #duration function. This function expects as parameters days, hours,minutes, seconds. Hours can be represented in numbers greater than 24. So if you split your text string into hours, minutes and seconds, you-re good to go 🙂

 

Capture.JPGusing the #duration function

 

Example pbix here: Example PBIX file

 

 

View solution in original post

4 REPLIES 4
Highlighted
Gravanita Regular Visitor
Regular Visitor

Re: Duration time MySQL

If you set the field to Duration you should be fine.

 

If you convert the duration to number and multiply by 24 you get the value in hours.

 

 

duration.JPG

In the image above you can see an example of importing some different durations (A1:A8).
For example, the last value that has duration 32h 25min can be seen in hours as 32.25.

 

 

Community Support Team
Community Support Team

Re: Duration time MySQL

Hi @robstra,

 

You can disable the option "Automatically detect column types and headers for unstructured sources" before you import data. Then you can change the data type to duration in power query later manully.

2.PNG

 

 

Reagrds,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Gravanita Regular Visitor
Regular Visitor

Re: Duration time MySQL

@v-frfei-msft

I went back to my answer to check your suggestion and I didn't manage to make it work. In the process, I created a text file with sample data. It seems that what I also suggested doesn't work when data is not coming in from excel.

duration text 2.JPGduration text 3.jpg

 

Checked the documentation on converting Duration from text Duration.FromText and it seems that it hh can't be greater than 23.

error 24.JPGhh can't be greater than 23.

Capture.JPGLargest duration expected

 

To represent duration with days it has to be in the format [-]ddd.hh:mm[:ss].

 

 

 

To answer the initial question, how to get the data inside powerbi as a duration, you can transform it  using the #duration function. This function expects as parameters days, hours,minutes, seconds. Hours can be represented in numbers greater than 24. So if you split your text string into hours, minutes and seconds, you-re good to go 🙂

 

Capture.JPGusing the #duration function

 

Example pbix here: Example PBIX file

 

 

View solution in original post

robstra Frequent Visitor
Frequent Visitor

Re: Duration time MySQL

I had access to procedure and modify it to return hours as decimal numbers. All related measures worked without any modification.

However your approach seems the best solution if the source is text.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,005)