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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

hh can't be greater than 23.hh can't be greater than 23.

Largest duration expectedLargest 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 🙂

 

using the #duration functionusing the #duration function

 

Example pbix here: Example PBIX file

 

 

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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

hh can't be greater than 23.hh can't be greater than 23.

Largest duration expectedLargest 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 🙂

 

using the #duration functionusing the #duration function

 

Example pbix here: Example PBIX file

 

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

 

 

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.