Excel Data-Cleansing: When Data CanNOT be Cleansed
3,926 views · Published 19 December 2017 · 5:20 · Indexed 20 September 2026
Channel: Excel On Fire (Oz du Soleil) · 2017 · Education
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: https://www.lynda.com/Excel-tutorials/Excel-2016-Get-Transform/608994-2.html Website: http://ozdusoleil.com My book: Guerrilla Data Analysis 2nd Edition http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336 My old blog: http://datascopic.net/blog-2-2
