cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RayRay
Microsoft
Microsoft

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

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!

image.pngimage.png

 
 
1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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.

 

image.png

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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.

 

image.png

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

View solution in original post

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

@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? 

 

 image.png

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors