decimal to duration conversion

[Anonymous]

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?

[Anonymous]

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]

@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]

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,

[AlB · Accepted solution]

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.