Forum Discussion
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 |
| 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
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
6 Replies
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
Nice kriscoupe I was not aware of the Text.*Delimiter funtions in PQ. IPGeorgiev you should go with this solution from kriscoupe
Awesome!!! Many many thanks for the support!
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
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=86400If 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=86400When 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.
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.
