Forum Discussion

IPGeorgiev's avatar
IPGeorgiev
Icon for Helper III rankHelper III
6 years ago
Solved

Convert HH:MM:SS to seconds

Hi All,   I have the following column in my table:   434:03:22 434:03:22 167:44:05 167:44:05 155:00:37 155:00:37 140:34:43 140:34:43 119:35:42 119:35:42 97:04:53 ...
Need Help
Tutorial Requests
  • kriscoupe's avatar
    kriscoupe
    6 years ago

    Hi IPGeorgiev,

     

    To avoid having to split the column by delimeter in the case you aren't sure of the length of each string being consistent always you can apply the same methods but using a combination of Text.BeforeDelimeter, Text.AfterDelimeter and Text.BetweenDelimeter M functions.

     

    Number.FromText( Text.BeforeDelimiter( [Time], ":" ) ) * 3600
+
Number.FromText( Text.BetweenDelimiters( [Time], ":", ":", 0, 0 ) ) * 60
+
Number.FromText( Text.AfterDelimiter( [Time], ":", 1 ) )

     

    Hope it helps.

     

    Kris

     

IPGeorgiev's avatar
IPGeorgiev
Icon for Helper III rankHelper III
6 years ago

Hi Pragati11 ,

 

when trying TIMEVALUE in Query Editor the function is not being recognized. Then if I try to add a new column in POwerBI Desktop it  returns an error. 

 

Here when I try with:

 

= Table.AddColumn(#"Changed Type4", "Custom", each [Talk Time]*86400)

 

 

Expression.Error: We cannot apply operator * to types Time and Number.
Details:
Operator=*
Left=12:03:17 AM
Right=86400

 

If I leave it just as text and not as time:

 

Expression.Error: We cannot apply operator * to types Text and Number.
Details:
Operator=*
Left=0:03:17
Right=86400

 

When I try this:

 

Column = MINUTE(Table[date time])*60+SECOND(Table[date time])

 

I get:

 

Cannot convert value '26:11:52' of type Text to type Number.

 

So no success with those two option as I have tried them prior to opening the thread.