Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

decimal to duration conversion

I have a duration column in the power query (query editor of PBI) like below:

But when I add this to the model, it is converted to decimal, like below:

How can I extract duration from this column by dax calculated column?

Does anyone know?

AlB
7 years ago
Hi Anonymous

I'm afraid you'll have to convert it to text since, as far as I know, Power BI doesn't yet handle fractions of a second in time format.
The decimal representation you have is in days. Taking that into account, you can convert it to the format you show (text) in a calculated column:

NewColumn = TRUNC(Table4[Zeit2 - Copy]) & "." & //Days FORMAT(TRUNC(Table4[Zeit2 - Copy]*24);"00") & ":" &//Hours FORMAT(TRUNC(Table4[Zeit2 - Copy]*24*60);"00") & ":" & //Minutes FORMAT(Table4[Zeit2 - Copy]*24*60*60;"00.0000000") //Seconds
You can make changes if you want to show it differently (fewer decimal places, etc. ) I've assumed the first digit before the dot is the day, correct?
In any case, I would recommend operating with the decimal value for whatever you need to do and only convert it to text at the end, when you have to display it.

4 Replies

Anonymous
Not applicable
7 years ago
you can use this:
duration = time(HOUR(Table2[Zeit2]),MINUTE(Table2[Zeit2]),SECOND(Table2[Zeit2]))

change the duration column type to "TIME"
Hope this helps.
- Anonymous
  Not applicable
  7 years ago
  Anonymous
  Thank you for your reply. This method only returns minutes and seconds, and it will cut out the milliseconds, which in this context are necessary
  - v-jiascu-msft
    Microsoft Employee
    7 years ago
    Hi Anonymous,
    
    AlB is right. There isn't a duration type in the data view while the Power Query has one. I would suggest you vote up this idea.
    
    Best Regards,