Power BI Seconds to Duration

While working with Power BI we often need to convert Seconds to Duration. This is easily done in Excel just by applying the formatting code “HH:MM:SS”. Unfortunately in Power Query or DAX, this is not possible. Here is the solution:

Seconds to Duration: Power Query

Power Query also means Get and Transform in Excel or Query Editor in Power BI Desktop.

Seconds column in Query editor

We have a column with seconds in it. The data type is Whole Number.

To convert it to duration – like D:HH:MM:SS we have to change the data type. This is done by adding a custom column.  Add column – Custom Column. Change the column name to whatever you want  and add this formula.

Seconds to Duration conversion formula

#duration is an intrinsic function. Four parameters: days, hours, minutes, seconds. Notice the ABC 123 data type next to the column name. This means “ANY” datatype in Query Editor. Change that to the Duration data type.

Seconds converted to hh:mm:ss          Duration data type applied

Power BI Desktop Import

When you import this data into Power BI Desktop, the duration value is converted to a decimal value – which appears to defeat the purpose. But wait … there is more.

Duration as decimal in Power BI Desktop

Now, apply Time data type to it and choose hh:mm: ss Format.

Apply Time data type and hh:mm:ss formatting

Finally, you what you want: Seconds to Duration.

Duration as HH:MM:SS
Yes. More complex compared to Excel. But that is how it is.

Disadvantage of Seconds to Duration conversion

Unfortunately, this My Duration cannot be plotted on a chart.

Data plotted; Duration cannot be plotted as a chart.

If you want to plot it, you will have to use the original number of seconds (the Sec column in this case). The duration can be used for text display in tooltips or tabular / matrix reports.

Power Query Formulas

You can also add separate columns for days, hours, minutes and seconds by using simple formulas. The Duration function allows you to get individual component from the #duration. For example, you can get hours from the duration using this formula:

=Duration.Hours( #duration(0,0,0, [Sec]) )

DAX formula

If you have a column containing seconds as a number, you can extract the Hours, Minutes, Seconds from it using simple mathematics.

DayPart = INT(Query1[Sec]/(24*60*60))
HoursPart = MOD(INT(Query1[Sec]/(60*60)),24)
MinPart = MOD(INT(Query1[Sec]/60),60)
SecPart = MOD( Query1[Sec],60)

Learn more about Data Analytics

Here is a list of Data Analytics articles I have written about Power BI. It covers multiple aspects including data import, clean-up, modelling, DAX, visualizations and dashboard creation.

Clock

 

Comments? Suggestions? Wish list?