This is Crap data at its worst–a dataset that cannot be cleansed. This video is a dose of Excel data-cleansing reality.
Someone asked for help cleansing a column of dates so they could be sorted. As I explored the data in Excel and Power Query to see what was wrong, I uncovered deeper problems.
There weren’t just formatting issues. One date had a year “20165.” So many red flags piled up and this is when the responsible thing to so is STOP! This data cannot be trusted. Send the data back to the source for verification and validation.
BTW: you’ll also see use of the Locale feature in Power Query (Get and Transform).
Join me at Patreon: https://www.patreon.com/Data
For an intro to Get & Transform (Power Query) try my Lynda.com course:
My book: Guerrilla Data Analysis 2nd Edition
My old blog: http://datascopic.net/blog-2-2