How to format times values greater than 24h

[Victor_Z]

Hello everybody

I know that Power BI don´t accept time greater than 24 hours and I need to use the data type duration in order to handle it and be able to show the values in different charts. 

However I am struggling with it. I have a time column (data is coming from Excel file - custom format of the column [h]:mm:ss)

that when changing the type to duration it gives the following error.

In the example value, 02/01/1900 5:00:00  ( 53 hours in the excel file) should be converted to 2.05:00:00

What am I missing?    

[goncalogeraldes · Accepted solution]

Hi @Victor_Z ,

If you need this to be solved on the Power Query side you can try the solution in the sample file that I attach.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Ahmedx]

or this variant

[Ahmedx · Accepted solution]

or

[Victor_Z]

Hi @Ahmedx 

Thanks for your help. The data I am getting when importing in power Query from Excel File is this format, for example 02/01/1900 5:00:00 (as excel start to count dates from 31/12/1989, therefore  instead of 53:00:00 as text format i am getting 02/01/1900 5:00:00) so your solution is missinh hours.

How can I get those "days" missing so I can have the total number of hours correctly?

[Ahmedx]

do you need to remove these lines?
if you don't need to delete, then show an example and the expected result.

[goncalogeraldes · Accepted solution]

Hi @Victor_Z ,

If you need this to be solved on the Power Query side you can try the solution in the sample file that I attach.

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Victor_Z]

Hi @goncalogeraldes 

Thanks for your reply, but finally your proposal is not working in my case. The data that i got from reading the excel file is a date&time format, so I am not getting 53:00:00 as text value per your example, but for 02/01/1900 5:00:00.

Therefore when doing the steps in Power Query i am missing lot of hours ( for this value, 48 hours exactly)

[amitchandak]

@Victor_Z , if you only need it table visual. Have first the duration in second and the you can use dynamic format String on the sum or avg measure

example https://youtu.be/qHALucET7N8?t=300

Other methods

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-i...
https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389