Forum Discussion
Convert MM:SS duration to HH:MM:SS
Hi all,
I have a column in my dataset with the following values in MM:SS
| Current situation | Desired situation |
| 71:05 | 01:11:05 |
| 50:30 | 00:50:30 |
| 01:25 | 00:01:25 |
| 05:55 | 00:05:55 |
| 80:54 | 01:20:54 |
I would like to change the values to HH:MM:SS so I can set the type to "time" or "duration" to do time based calculations. Now I get an error for values in row 1 and 5 because there are more then 60 minutes in the field.
Is it possible to change this in Power Query?
Anonymous Sure, you could split the column using the colon, do some arithmatic like divide/modulus and then recombine them into a single field.
Use below formula where Current is column name
= [m=Number.From(Text.Split([Current],":"){0}), s=Number.From(Text.Split([Current],":"){1}),t=#time(Number.IntegerDivide(m,60),Number.Mod(m,60),s)][t]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMje0MjBVitWJVjI1sDI2ALMMrYygQlamEIaFgZWpiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Current = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each [m=Number.From(Text.Split([Current],":"){0}), s=Number.From(Text.Split([Current],":"){1}),t=#time(Number.IntegerDivide(m,60),Number.Mod(m,60),s)][t], type time) in #"Added Custom"
2 Replies
Use below formula where Current is column name
= [m=Number.From(Text.Split([Current],":"){0}), s=Number.From(Text.Split([Current],":"){1}),t=#time(Number.IntegerDivide(m,60),Number.Mod(m,60),s)][t]See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMje0MjBVitWJVjI1sDI2ALMMrYygQlamEIaFgZWpiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Current = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each [m=Number.From(Text.Split([Current],":"){0}), s=Number.From(Text.Split([Current],":"){1}),t=#time(Number.IntegerDivide(m,60),Number.Mod(m,60),s)][t], type time) in #"Added Custom"Anonymous Sure, you could split the column using the colon, do some arithmatic like divide/modulus and then recombine them into a single field.