Duration time MySQL

[Anonymous]

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?

[v-frfei-msft]

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.

Reagrds,

Frank

[Anonymous · Accepted solution]

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

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.

Largest 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 function

Example pbix here: Example PBIX file

[Anonymous]

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]

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.

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.