Forum Discussion
Convert hr/min/s to seconds
I have this data set where the numbers have been input as mostly minutes and seconds, with some having hours, mins and seconds (highlighted).
I need to convert it to a usable format so I can compare the talk times for different columns not shown in the screenshot (ie date, day, time of day etc.)
In Powerquery, I used the GUI split by delimiter function to split using the colon into three different columns in order to convert it all to seconds and add later, however this puts the hour into the same column as minutes.
I have also considered using the following custom column but it doesn't work:
if (Text.Contains([Total Talk Time], >1 ":")) then Text.Insert("00:") else ""
How should I go about solving this problem?
Hi Anonymous ,
Add a new column then change type to Whole Number
[ c = List.Count(Text.PositionOf([Total Talk Time], ":", Occurrence.All)), // how many : t = Time.FromText( if c = 1 then "00: " & [Total Talk Time] else [Total Talk Time]), // convert to Time type s = Time.Hour(t) * 3600 + Time.Minute(t) * 60 + Time.Second(t) ][s]Output:
Or do it using DAX:
Column = var _replace=SUBSTITUTE([Total Talk Time],":","") var _count= LEN([Total Talk Time]) - LEN(_replace) var _time= CONVERT(IF(_count=1, "00: "&[Total Talk Time],[Total Talk Time]),DATETIME) return HOUR(_time) *3600+ MINUTE(_time) *60+SECOND(_time)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
You can use either of the formulas
= Number.From(Time.From(if List.Count(Text.Split([Total Talk Time],":"))=2 then "00:"&[Total Talk Time] else [Total Talk Time]))*24*60*60 = Duration.TotalSeconds((Time.From(if List.Count(Text.Split([Total Talk Time],":"))=2 then "00:"&[Total Talk Time] else [Total Talk Time]))-#time(0,0,0))See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMrZUitWJVjIwszI1AbMMjayMjSEsKxDbVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total Talk Time" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each Number.From(Time.From(if List.Count(Text.Split([Total Talk Time],":"))=2 then "00:"&[Total Talk Time] else [Total Talk Time]))*24*60*60), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Duration.TotalSeconds((Time.From(if List.Count(Text.Split([Total Talk Time],":"))=2 then "00:"&[Total Talk Time] else [Total Talk Time]))-#time(0,0,0))) in #"Added Custom1"You can try this custom column (where #"Previous Step" is the obvious:
let #"Previous Step" = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcrBDQAgCATBXnj7gAONXiuG/tvQwG+y2XvFlH4kx5cxrKSTjm5BaIsG+ipjM7xPEFsyHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Total Talk Time" = _t]), #"Added Custom" = Table.AddColumn(#"Previous Step", "Custom", each let elements= List.Transform( List.Reverse( Text.Split([Total Talk Time],":")), each Number.From(_)), sec = elements{0}, min = elements{1}, hrs = try elements{2} otherwise 0 in #duration(0,hrs,min,sec), type duration) in #"Added Custom"Or, in the Custom Column dialog from the UI:
but you'd have to add a step to set the data type to duration
Hi Anonymous ,
Add a new column then change type to Whole Number
[ c = List.Count(Text.PositionOf([Total Talk Time], ":", Occurrence.All)), // how many : t = Time.FromText( if c = 1 then "00: " & [Total Talk Time] else [Total Talk Time]), // convert to Time type s = Time.Hour(t) * 3600 + Time.Minute(t) * 60 + Time.Second(t) ][s]Output:
Or do it using DAX:
Column = var _replace=SUBSTITUTE([Total Talk Time],":","") var _count= LEN([Total Talk Time]) - LEN(_replace) var _time= CONVERT(IF(_count=1, "00: "&[Total Talk Time],[Total Talk Time]),DATETIME) return HOUR(_time) *3600+ MINUTE(_time) *60+SECOND(_time)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
