cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

decimal to duration conversion

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

 

یعقشفهخد1.PNG

 

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

 

decimal.PNG

 

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

Does anyone know?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: decimal to duration conversion

Hi @elahemeydani

 

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 4
ASN_SATYA Frequent Visitor
Frequent Visitor

Re: decimal to duration conversion

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.

Re: decimal to duration conversion

@ASN_SATYA

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

Super User
Super User

Re: decimal to duration conversion

Hi @elahemeydani

 

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.

 

 

Community Support Team
Community Support Team

Re: decimal to duration conversion

Hi @elahemeydani,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.