Site logo white shadow

Converting “Seconds” to “Duration” in Power BI

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.



8 Responses

  1. I’m using Power Bi for the first time and my first task has been to convert my excel report with about 20 duration fields in it to a power BI report. After fixing a few import issues I started to notice my time columns were not usable. I’ve spent a day running through these duration problems and your article was exactly what I was looking for – a straight-forward step-by-step tutorial of how to import correctly the first time! Thank you!!

  2. HI Dr Nitin,
    I have question.my excel column is duration 0:0:0 but when import to powerBi, the duration change to 31/12/1899 12:01:13am. how do i convert it back to duration?

  3. I get the following error:
    Expression.Error: We cannot apply field access to the type Number.
    Key=Total Calls Duration

Queries | Comments | Suggestions | Wish list

Subscribe to Blog

Join 1,758 other subscribers

Popular articles

Pivot Table Pro Course

Yes. You use Pivot Tables everyday. Now it is time to find out the real power and nuances. 5.5 hours video, exercises, samples, Q&A.

Excel to Power BI Course

Learn Power BI using the concepts you already know in Excel. Fast transition, in-depth coverage and immediately usable.