Convert hr/min/s to seconds

[Anonymous]

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?

[Anonymous · Accepted solution]

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.

[ronrsnfld]

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

[Vijay_A_Verma]

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"