Forum Discussion

Anonymous's avatar
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 dur...
Need Help
  • AlB's avatar
    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.

     

     

Anonymous's avatar
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's avatar
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's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft 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,

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    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.