Forum Discussion
Cleaning up data entry - invalid dates - cannot use CALENDAR or CALENDARAUTO
We've got a datasource in Google Sheets with hand entered date/time values - sometimes erroneous. Ultimately, my goal is to setup a calendar table with relationship etc. What's the recommended way to clean these date values to enable my goal? Should I replace the erroneous values with null? Going back to the raw data and correcting by hand is not feasible due to the magnitude of the dataset - it spans a few years and thousands of entries.
Even more challenging are incorrect dates that map to date values rather than generating an error, but are not acceptable because they might go back centuries based on how it was entered, i.e. missing a digit in the year value.
Any input greatly appreciated.
First: You probably aren't going to find all the dates that are wrong. Next, you need to decide what to do with the wrong dates. Mapping them to null or a flag value (like 1/1/1900) is one option. Consider how you actually want to present your dashboard. Presumably, date error entries should show up in things like cards and tables, but would be dropped for time series charts?
I would create a second column, cleaned_date. If there was a context displaying individual row date values, you would still have the original. The new column would be based on a series of tests, so if the value could not be turned into a valid date, or if it was out of bounds (far past or far future) it would use null, otherwise return the date value.
There are other tricks - if the dates should be (roughly) in order, you can look for outliers. However, this is hard to do without having the errored dates tripping up nearby dates. It depends on your taste for errors as it may be squeezing relatively few errors out for a lot of effort. I would skip this unless it proves absolutely necessary.
The other problem to consider is user education. People may question why the counts between visuals don't completely align. Something like a "data quality" card showing 95% of records have a valid date may help.
1 Reply
First: You probably aren't going to find all the dates that are wrong. Next, you need to decide what to do with the wrong dates. Mapping them to null or a flag value (like 1/1/1900) is one option. Consider how you actually want to present your dashboard. Presumably, date error entries should show up in things like cards and tables, but would be dropped for time series charts?
I would create a second column, cleaned_date. If there was a context displaying individual row date values, you would still have the original. The new column would be based on a series of tests, so if the value could not be turned into a valid date, or if it was out of bounds (far past or far future) it would use null, otherwise return the date value.
There are other tricks - if the dates should be (roughly) in order, you can look for outliers. However, this is hard to do without having the errored dates tripping up nearby dates. It depends on your taste for errors as it may be squeezing relatively few errors out for a lot of effort. I would skip this unless it proves absolutely necessary.
The other problem to consider is user education. People may question why the counts between visuals don't completely align. Something like a "data quality" card showing 95% of records have a valid date may help.