Forum Discussion
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. ...
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.
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.
`#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}),