Forum Discussion
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.
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?
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.
3 Replies
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}),
hola aqui hay un video tal vez te da alguna idea
