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

Can't use SQL TIME as Duration

I need to display a duration (hh:mm:ss.mmm) in a grid and in a chart.

 

I have a SQL view where I use a date difference to generate a Duration field converting to Time:

 CONVERT(TIME,r.DateUpdated-r.DateRun) as Duration,

 

The view correctly reports this as a Duration field (hh:mm:ss.mmm):

 

Then I import the view to Power BI Desktop.

The duration is treated as a Time field and shown as 12:XX:YY AM by default (but the underlying data HH:MM:SSS.MMM is understood, as you can see from the filter below):

 

 

If I transform the column into a Duration, I get ERRORS.

 

 

 

To fix this, I can transform the field in text first and then into duration, and it shows the correct thing on the query editor:

 

 

But now if I go to the PowerBi window, I see this as a decimal number:

 

 

So in my charts are shown as a decimal point, now I completely lost the concept that the duration was 6 minutes, I only have a 0.00!

 

 

This is making me nuts, how can I visualize a SQL field of type TIME in the correct format of HH:MM:SS.MMM on Power BI?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Steve_Wheeler Established Member
Established Member

Re: Can't use SQL TIME as Duration

There is further detail on issues with Duration data type in Power BI on this thread, http://community.powerbi.com/t5/Desktop/Format-the-average-of-a-duration/m-p/95816#M40368

 

It includes a DAX function to display a decimal version of a Power Query Duration data type in D.HH:MM:SS format.  That might be extended for what you need but is restricted for use as text display only, not a numeric measure on charts etc.

 

 

View solution in original post

Maurgi Frequent Visitor
Frequent Visitor

Re: Can't use SQL TIME as Duration

The solution is that this is not supported and you should vote for a proper feature here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

 

In the meantime, I will add two columns one as text for the table and one as decimal for the graph.

View solution in original post

4 REPLIES 4
MarcelBeug Super Contributor
Super Contributor

Re: Can't use SQL TIME as Duration

Edit: I just realized the following won't too helpful as the result will be a decimal number anyhow after loading, as you already discovered,

So apologies.

 

You can transfom time to duration by subtracting #time(0,0,0) from your Duration field (which is formatted as time), like:

= Table.TransformColumns(Typed, {"Duration", each _ - #time(0,0,0), type duration})

 

where "Typed" is the name of the preceding step.

Specializing in Power Query Formula Language (M)
Steve_Wheeler Established Member
Established Member

Re: Can't use SQL TIME as Duration

There is further detail on issues with Duration data type in Power BI on this thread, http://community.powerbi.com/t5/Desktop/Format-the-average-of-a-duration/m-p/95816#M40368

 

It includes a DAX function to display a decimal version of a Power Query Duration data type in D.HH:MM:SS format.  That might be extended for what you need but is restricted for use as text display only, not a numeric measure on charts etc.

 

 

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Can't use SQL TIME as Duration

Another suggestion from my side would to include 2 columns for your durations: one as a duration (which will become a decimal number) and one as a time value, formatted as HH:mm:ss.

Now you can create your visual, using the decimal values as Y-values and add the time values as legend values.

As a result, you will have your time values in the tooltips, formatted as HH:mm:ss.

Drawback is that you need to adjust colors for all time values.

You can hide the legend as such.

Maybe you also want to increase the number of decimals on your Y-axis or otherwise just hide the Y-axis.

This is probably not an ideal solution but at least it's a solution.

Specializing in Power Query Formula Language (M)
Maurgi Frequent Visitor
Frequent Visitor

Re: Can't use SQL TIME as Duration

The solution is that this is not supported and you should vote for a proper feature here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8814178-field-of-duration-type

 

In the meantime, I will add two columns one as text for the table and one as decimal for the graph.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)