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.
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.
#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.
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.
Now, apply Time data type to it and choose hh:mm: ss Format.
Finally, you what you want: Seconds to Duration.
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.
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]) )
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.