Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 comp...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
ronrsnfld
Icon for Super User rankSuper User
4 years ago

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