Delete a row

[ben7]

Hi all,

I have some oddly formatted data taken from government data. For some reason, they have inserted a random row into the data ("February 2019" during the 2014 months). How would I got about deleting this row? Is there syntax that allows for identifying: IF "February 2019" is after "Tasmania" then filter for "February 2019"?

Thanks in advance!

[lmorrell · Accepted solution]

Hi @ben7 

Solution is attached. 

The logic to remove one row based on the contents of an above row can be done with the Multi-Row Formula tool. By flagging the row you want to remove, and then filtering afterwards, you can remove a single row. If the State and Date column were to be called "Left Hand Column" then you can use the below formula:

if [Row-1:Left Hand Column] = 'Tasmania' AND [Left Hand Column] = 'February 2019' then 1
else 0 
endif

However, I did notice that a different issue could be that a single column contains two different kinds of data (ie. State AND Date) - if you wanted to strike two birds with one stone then you can use a datetime tool to verify any valid dates, move all dates into a separate column, and then remove all the date rows from the states to simultaneously remove the random 2019 row while also cleaning the data structure. 

Hope this helps!