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:
Contents
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]) )
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
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!!
this was what i was looking for, i am so glad to be here, thank you
Could you use an example where the duration is bigger than 24 hours, please?
It works in the same way. The #duration has the syntax – #duration(days as number, hours as number, minutes as number, seconds as number) as duration.
Details here: https://docs.microsoft.com/en-us/powerquery-m/sharpduration
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?
In Power Query, while importing it in Excel, keep it as TIME data type.
I get the following error:
Expression.Error: We cannot apply field access to the type Number.
Details:
Value=0
Key=Total Calls Duration
Check data type of original column.