Issues transforming text to duration

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.