Error converting certain HH:MM vlaues to type "Duration"

[RayRay]

Hi, Im importing a column from an excel which has duration values in format HH:MM. When intially pulled in it is type "Any" by default. When I try convert to "Duration" it looks like anything over 24 hours comes up with an error. What is the best way to handle this data type so that I can then sum the total hours in my report? 

I also dont want to roll over to days either when I sum it in the report if possible. 

Thanks!

[Mariusz · Accepted solution]

Hi @RayRay 

You can add custom column like 

let split = Text.Split( [Column1], ":" ) in #duration( 0, Number.FromText( split{0} ), Number.FromText( split{1} ), 0 )

 please see the result for ref.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[RayRay]

Yup seems to do the job. Thanks @Mariusz   

also what I needed to do was, while building the report, just change the type to time and format to HH:mm

[RayRay]

@Mariusz , I was looking at this again, and while the code helps to convert the data into the "duration" format, when I try use the data to build a report I can't seem to get it in the format that I need ie. for the larger values eg 1.20:30 (1 day and 20 hours and 30 min) needs to be in format 44:30. 

example below - Column 1 is the orginal import (data type = text) Column 2 is transformed data (Duration)  I changed the data type to Time, format hh:mm but it just drops the extra 24 hours. 

Am I missing something?