Forum Discussion

Brazen_BI's avatar
Brazen_BI
Frequent Visitor
2 years ago
Solved

Convert Text (x Hours, xMinutes, xSeconds) to duration

Attempted unsuccessfully to convert text from column labeled "Handled Duration" into duration type with format "00:00:00". Purpose is to eventually use this column to calcuate total and average handl...
Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi, Brazen_BI 

     

    =Time.From(
       Text.Combine(
           List.Transform(
                  Text.Split([Handled Duration],", "),
                  each Text.BeforeDelimiter(_," ")),
           ":")
    )

    or

    #time(
       Number.From(Text.BeforeDelimiter([Handled Duration]," Hours")),
       Number.From(Text.BetweenDelimiters([Handled Duration],"Hours, "," Min")),
       Number.From(Text.BetweenDelimiters([Handled Duration],"Minutes, "," Sec")))

     

    Stéphane 

slorin's avatar
slorin
Icon for Super User rankSuper User
2 years ago

Hi, Brazen_BI 

 

=Time.From(
   Text.Combine(
       List.Transform(
              Text.Split([Handled Duration],", "),
              each Text.BeforeDelimiter(_," ")),
       ":")
)

or

#time(
   Number.From(Text.BeforeDelimiter([Handled Duration]," Hours")),
   Number.From(Text.BetweenDelimiters([Handled Duration],"Hours, "," Min")),
   Number.From(Text.BetweenDelimiters([Handled Duration],"Minutes, "," Sec")))

 

Stéphane 

Brazen_BI's avatar
Brazen_BI
Frequent Visitor
2 years ago

Thanks. I combined this solution with a measure to calculate average duration time to get the final visual I needed: 

Average Handle Time Duration = FORMAT(AVERAGE('Table'[Column]),"HH:MM: SS")