Yesterday while cleaning my data for the Preppin' Data 2023 Week 4 Challenge, I ran into what seemed like a straightforward task: changing a text column into a date format. Solving the problem eventually helped me realize that part of our work as data analysts is learning to think in a way that machines can understand.
The Problem
The 'Date of Birth' column in my table is formatted as text (or string). I need to change the data type for this column to 'Date.'
My Attempt at a Solution
My first approach–simply selecting 'Date' from the data type options–didn't work. I then realized that the problem lay in the difference in date formats. My original dates were in the American style MM/DD/YYYY, while I needed them to be in the UK style DD/MM/YYYY.
To bridge this gap, I used the 'Change Type with locale' function and set the format to UK format DD/MM/YYYY.
The result? The dreaded sea of errors. : /
The Solution & What I Learned
After a conversation with my partner–who happens to be a computer programmer–I finally realized the issue: I had been asking Power Query for the date format I wanted, but what I should have done was specify the original format (US) so that it could be accurately converted.
In the realm of data processing, a computer perceives everything as mere characters. It doesn't possess an innate understanding that some of these characters represent dates, let alone being able to distinguish between US-style and other date formats. To make sense of it all, you must act as a guide, showing the computer how to decode the data.
As my colleague Ollie Clarke aptly puts it: Rather than specifying your desired output, you must specify the original format–the language the data speaks–to help the computer make sense of it all.