Forum Discussion

jmeccles's avatar
jmeccles
Helper I
5 years ago
Solved

How to Convert Text (HH:MM:SS) to Duration in Power Query

Hi there! I'm trying to change a text column [with entries in the format HH:MM:SS - see "Resolution Time (in Hrs)" in screenshot] of my report into a duration column but I'm unsure of how to. ...
Need Help
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, jmeccles 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may add a new step with the following m codes.

    = Table.TransformColumns(#"Changed Type",{"Resolution Time",each 
let txt=_,
h=Number.From( Text.Start(txt,2)),
mm=Number.From( Text.Middle(txt,3,2)),
ss=Number.From( Text.End(txt,2)),
dd=Number.IntegerDivide(h,24),
hh=Number.Mod(h,24)
in 
#duration(dd,hh,mm,ss),
type duration
}
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.