Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Awesome!!! Many many thanks for the support!
Nice @kriscoupe I was not aware of the Text.*Delimiter funtions in PQ. @IPGeorgiev you should go with this solution from @kriscoupe
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.
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=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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |