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

[jmeccles]

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.
Duration Transformation InaccessibleI hoped that the Duration transformation could assist but it doesn't appear to be accessible.

Any ideas on how I could go about doing this?

[v-alq-msft · Accepted solution]

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.

[Tbemrose]

`#duration` will handle the math. This is cleaner:

DurationHHMMSS = each let d = List.Transform(Text.Split(_, ":"), Number.FromText) in #duration(0, d{0}, d{1}, d{2}),
TransformedTable = Table.TransformColumns(Source, {ColumnName, DurationHHMMSS, type duration}),

[pedrovr2020]

hola aqui hay un video tal vez te da alguna idea

https://youtu.be/DQDBB_0E9xQ?t=2