Convert HH:MM:SS to seconds

[IPGeorgiev]

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

97:04:53

71:19:41

71:19:41

69:05:21

69:05:21

52:44:04

52:44:04

49:01:43

49:01:43

48:09:00

48:09:00

41:47:45

41:47:45

40:30:38

40:30:38

32:24:03

32:24:03

30:20:36

30:20:36

28:31:04

28:31:04

27:55:50

27:55:50

27:47:49

27:47:49

26:54:21

26:54:21

25:42:04

25:42:04

23:29:16

23:29:16

16:46:36

16:46:36

16:34:27

16:34:27

16:32:49

16:32:49

16:12:34

16:12:34

15:07:40

15:07:40

14:50:55

14:50:55

14:39:05

14:39:05

14:27:47

14:27:47

13:59:26

13:59:26

13:42:18

13:42:18

12:29:26

12:29:26

12:05:51

12:05:51

11:56:36

11:56:36

11:55:00

11:55:00

11:39:40

11:39:40

11:38:23

11:38:23

11:33:39

11:33:39

10:42:57

10:42:57

10:38:30

10:38:30

10:30:30

10:30:30

10:30:25

10:30:25

10:29:18

10:29:18

10:06:52

10:06:52

9:58:55

9:58:55

9:24:37

9:24:37

9:17:43

9:17:43

9:02:56

9:02:56

8:25:35

8:25:35

8:03:47

8:03:47

5:19:24

5:19:24

4:52:42

4:52:42

4:52:08

I need to be able to convert this to seconds. No matter what I try (TIMEVALUE etc) I get either errors or no result 😞

Is this even possible? The Power Query recognizes this as a text.

many thanks in advance!

Best regards,
Ivan 

[kriscoupe · Accepted solution]

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]

Awesome!!! Many many thanks for the support!

[jdbuchanan71]

Nice @kriscoupe I was not aware of the Text.*Delimiter funtions in PQ.  @IPGeorgiev  you should go with this solution from @kriscoupe 

[jdbuchanan71]

In PowerQuery you can add a custom column like this.

Number.FromText (Text.Start([Time], Text.Length ([Time]) - 6 ) ) * 3600
+
Number.FromText (Text.Start ( Text.End ( [Time], 5), 2)) * 60
+
Number.FromText ( Text.End ([Time] , 2) )

 

This assumes that the minutes and seconds are ALWAYS 2 characters.  If not you would want to split the time by delimeter (:) the do the conversion on each then add them back up.

[Pragati11]

Hi @IPGeorgiev ,

There are some existing threads for this query:

https://community.powerbi.com/t5/Desktop/Converting-HH-MM-SS-to-seconds/m-p/674207

https://community.powerbi.com/t5/Desktop/How-to-convert-HH-MM-SS-to-seconds-using-DAX/m-p/857997

Thanks,

Pragati

[IPGeorgiev]

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.