Can't use SQL TIME as Duration

[Maurgi]

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?

[Anonymous · Accepted solution]

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.

[MarcelBeug]

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.

[Anonymous · Accepted solution]

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.

[Maurgi · Accepted solution]

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.

[MarcelBeug]

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.