Trying to covert a text string to duration in a column. problem is I have some values that are mm:ss.zzz and others that are just ss.zzz I know it needs to be hh:mm:ss.zzz but not sure how to add the missing hours and minutes if they are not present. example
| Athlete | Interval Time | Rank |
|---|---|---|
| Joe | 31.323 | 1 |
| Jeff | 1:02.323 | 2 |
Once I can get the duration set I can set rankings by lowest time and How far behind.
You can try:
let
nth = List.Count(Text.PositionOf([Interval Time], ":", Occurrence.All )),
pad = if nth = 0 then "0:0:" else if nth = 1 then "0:" else ""
in
Duration.FromText(pad & [Interval Time])
You can try this:
Paste this code into the Advanced Editor to see how it transfroms what you show to what you want
let
//Change Source to your actual data source
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRMjbUMzYyBjIMlWJ1gIKpaWkgjpWBEVTcSCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Athlete = _t, #"Interval Time" = _t, Rank = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Athlete", type text}, {"Interval Time", type text}, {"Rank", Int64.Type}}),
//Change time column to actual duration
#"Duration" = Table.TransformColumns(#"Changed Type",{"Interval Time", each
[a=Text.Split(_,":"),
b=List.Repeat({"00"}, 3-List.Count(a)),
c=b & a,
d = Text.Combine(c,":"),
e=Duration.FromText(d)][e], type duration
})
in
Duration
