Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Multiple Cols Convert Txt HHH:MM:SS to Duration (Hrs)

Hi I have multiple colums for txt HHH:MM:SS and i'd like to convert to Duration (Hours), any help would be appreciated.      
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

Anonymous , You need to deal it with in calculation, hour will not display more than 24 hours

 

Best it is to split that in-power query into 3 columns

 

Duration
https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageKey=814a2cb4-3cca-4cd1-a620-c467adeaaaf6&CommunityKey=b35c8468-2fd8-4e1a-8429-322c39fe7110&tab=digestviewer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks, does that mean i need to split all colums? in Excel you can format [H],MM,SS to show more that 24hrs, can you not do this in Power BI?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    According to your screenshot, I think your columns should be in text data type due to your hour is larger than 24. So we couldn't use Hour() to get hour value. Here I suggest you to use Split function to get hour value before first ":".

     

    Best Regards,
    Rico Zhou

     

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