Forum Discussion
Convert text data type to duration data type
Hi All,
I am trying to convert a text field to a duration, but my data comes in like this:
07:53.0
01:46.6
05:30.7
00:40.3
39:22.0
The format is mm:ss.0
I need to be able to sort from highest to lowest for time comparison. Hence I need to be able to convert it to duration data type.
The sort function is not working at the moment, as it is text.
I prefer to do the transformation in power query if possible.
Thanks all.
Cheers,
Kev
Hey BigKev ,
this will be a little more complex than one might expect 😉
First you have to create 3 column that contain the minutes, the seconds, and the fraction of a second. You can use the Split by Delimiter operationThen you can compute a fourth column that contains a decimal value representing seconds, you can use this value for sorting.
Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom
1 Reply
Hey BigKev ,
this will be a little more complex than one might expect 😉
First you have to create 3 column that contain the minutes, the seconds, and the fraction of a second. You can use the Split by Delimiter operationThen you can compute a fourth column that contains a decimal value representing seconds, you can use this value for sorting.
Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom