Forum Discussion

An_dy's avatar
An_dy
Frequent Visitor
2 years ago
Solved

Bulk replace - Text to Time

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,

  • you can convert such data to normal time like this

    and then convert them into seconds

1 Reply

  • you can convert such data to normal time like this

    and then convert them into seconds