Forum Discussion
Issues transforming text to duration
I'm still learning Power BI and I'm facing an issue with one specific column that I can't solve.
I have a column that gives me the total amount of hours spent on an activity. The format is dd:hh:mm:ss. When I try to convert it to a duration type, any value over 24h returns an error.
I'm trying to extract just the total number of hours spent.
Has anyone faced a similar issue? How can I solve this?
Hi ViníciusLacerda ,
Thank you for reaching out to the Microsoft fabric community forum.
Thank you for providing the details of your scenario. The column you’re working with uses the dd:hh:mm:ss format, which can cause problems when converting to the Duration type in Power Query, since it only accepts hh:mm:ss. If the hour value is greater than 24, errors may occur.
The guidance already shared by Omid_Motamedise and FBergamaschi is correct, ensuring consistent formatting is important, and looking into duration conversions is a good approach. To get directly to your requirement of extracting the total number of hours spent, you can transform the column in Power Query using the following steps:
Option 1 - Using Mcode :
let Split = Text.Split([Carga Horária Executada], ":"), Days = Number.From(Split{0}), Hours = Number.From(Split{1}), Minutes = Number.From(Split{2}), Seconds = Number.From(Split{3}), TotalHours = Days * 24 + Hours + Minutes / 60 + Seconds / 3600 in TotalHoursOption 2 - Using the Power Query interface (no coding):
- First, select the Carga Horária Executada column.
- Then, go to Home → Split Column → By Delimiter on the ribbon and split the column using the ":" delimiter.
- This will generate four new columns: Days, Hours, Minutes, and Seconds. Change the data type of each to Whole Number.
- Next, add a Custom Column using the following formula:
[Days] * 24 + [Hours] + [Minutes] / 60 + [Seconds] / 3600- Change the TotalHours column type to Decimal Number, or select Whole Number if you do not require fractional hours.
- If the temporary split columns are unnecessary, you can remove them.
This will give you the total hours, even for durations longer than 24 hours. I suggest testing both methods to determine which one works best for your workflow.
Hope this helps, please feel free to reach out for any further questions.
Thank you.
4 Replies
Here there are several ways
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Make sure your column format is consistent. if your format is dd:hh:mm:ss, you shouldn't have any value of hour greater than 24, it should be added to the day part.
Could you please double check the format and share here what is the format you used or share the raw data?
Hi ViníciusLacerda ,
Thank you for reaching out to the Microsoft fabric community forum.
Thank you for providing the details of your scenario. The column you’re working with uses the dd:hh:mm:ss format, which can cause problems when converting to the Duration type in Power Query, since it only accepts hh:mm:ss. If the hour value is greater than 24, errors may occur.
The guidance already shared by Omid_Motamedise and FBergamaschi is correct, ensuring consistent formatting is important, and looking into duration conversions is a good approach. To get directly to your requirement of extracting the total number of hours spent, you can transform the column in Power Query using the following steps:
Option 1 - Using Mcode :
let Split = Text.Split([Carga Horária Executada], ":"), Days = Number.From(Split{0}), Hours = Number.From(Split{1}), Minutes = Number.From(Split{2}), Seconds = Number.From(Split{3}), TotalHours = Days * 24 + Hours + Minutes / 60 + Seconds / 3600 in TotalHoursOption 2 - Using the Power Query interface (no coding):
- First, select the Carga Horária Executada column.
- Then, go to Home → Split Column → By Delimiter on the ribbon and split the column using the ":" delimiter.
- This will generate four new columns: Days, Hours, Minutes, and Seconds. Change the data type of each to Whole Number.
- Next, add a Custom Column using the following formula:
[Days] * 24 + [Hours] + [Minutes] / 60 + [Seconds] / 3600- Change the TotalHours column type to Decimal Number, or select Whole Number if you do not require fractional hours.
- If the temporary split columns are unnecessary, you can remove them.
This will give you the total hours, even for durations longer than 24 hours. I suggest testing both methods to determine which one works best for your workflow.
Hope this helps, please feel free to reach out for any further questions.
Thank you.
Thank you so much Anonymous , Omid_Motamedise and FBergamaschi , it worked perfectly. You guys saved me so much time.
