Forum Discussion

TNewton's avatar
TNewton
Regular Visitor
3 years ago

Minutes:Seconds conversion

Hello all!

 

I am very new to Power BI and trying to convert our reports from Excel to Power BI. My issue is that our reports export measure time in duration, where the time format is for example 1436:50 (1436 minutes and 50 seconds). I know how to work this in Excel, but anyone know how to convert this data into the proper HH:MM:SS format (23:56:50)? 

 

As always thanks in advance!

Need Help

3 Replies

  • djurecic's avatar
    djurecic
    Super User
    3 years ago

    Hi TNewton 

     Here is one way to do this. First, select data view 

     

    Then select the column you'd like to change and click Format in the ribbon- you will see a long list of formats including time formats.

    Please consider accepting as solution if this answers the question- thanks!

     

     

     

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    The best way to handle durations is to convert it to a decimal (in days), load your table, write your DAX measures to do aggregation, and then format it at the end. You can do the first step with a custom column in the query editor with this expression.

     

    = Number.FromText(Text.BeforeDelimiter([Duration], ":"))/24/60 + Number.FromText(Text.AfterDelimiter([Duration], ":"))/24/60/60

     

    Then use the approach in this article to FORMAT as a time value however you like.

    Calculate and Format Durations in DAX – Hoosier BI

     

    Pat

     

  • TNewton's avatar
    TNewton
    Regular Visitor
    3 years ago

    Thank you to you both! I have another project I have to complete so I have put this one on hold, but wanted to express my graditude for all your time on this. I will try the above out and check back. 

     

    Again thank you!