Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
I 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?
Solved! Go to 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.
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