Bulk replace - Text to Time

[An_dy]

Hi All,

I am working with a dataset that has a text field with talk time. The fields will show values like below

00:55:23

00:54:26

etc

I have a calculated column that turns that value in to seconds. So for example, 00:55:23 turns in to 3323 seconds. 

My problem is that sometimes my dataset imports time values that don't make a ton of sense. For example, we will see something like 00:60:23. When this type of value pops up my calculated column errors out and the visual does not display correctly. I can fix this by going in to query editor and manually adding a filter for that partiular value or replacing it to something that makes sense. 

These values will pop up randomly and break the dashboard until I can manually go in and fix it. 

Is there a way to add a step in the query editor that will auto replace based on a range? For example. anything between 00:60:00 - 00:90:00 change this to 00:59:00. 

Thanks,

[Ahmedx · Accepted solution]

you can convert such data to normal time like this

and then convert them into seconds