Convert text data type to duration data type

[BigKev]

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

[TomMartens · Accepted solution]

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 operation

Then 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