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
TotalHours
Option 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.
