Parsing Dates & Regional Settings
Learn why simply changing a column to Date type can go wrong, how regional settings affect date interpretation, and how the Parse function gives you cleaner, safer results.
Date columns are one of the trickiest parts of data cleaning. Raw files often contain non-date values mixed in — like "TOTAL" rows — that Power Query can silently misinterpret when you apply a Date type conversion. The result looks correct at first glance but contains wrong data that will corrupt your analysis downstream.
This lab also introduces regional settings — the locale Power Query uses to decide what date format to expect (e.g. MM/DD/YYYY vs DD/MM/YYYY). Instead of the standard type-change button, you'll use Transform → Date → Parse, which is more explicit and converts only values that genuinely match a date pattern, leaving everything else as an error you can then filter out safely.
| # | Steps | Directions |
|---|---|---|
| 1 | Create a new query and reference CSV File 1C. | |
| 2 | Change the Source step so that it doesn't specify number of columns. | Delete Columns=19, from the formula bar. |
| 3 | Delete 4 unwanted header rows from CSV data. | |
| 4 | Identify correct headers from the data. | |
| 5 | Delete Ref1, Ref2 and Metric columns. | |
| 6 | Highlight Store & Cat columns, and unpivot the rest. | |
| 7 | Rename Attribute & Value columns as Date and Sales, respectively. | Double-click on the column headers to rename them. |
| 8 | Before we start working with dates, check your date region settings. | |
| 9 | Try it first: Change the Date column to Date type by clicking the ABC datatype icon next to its name. Notice that in row 17, the 2017 TOTAL value is incorrectly interpreted as a date. We don't want this — delete this step. | |
| 10 | Instead, use the Parse function to interpret dates. | |
| 11 | Filter out the error rows and change the data types of Store and Cat to Whole Number. | |
| 12 | Rename the query to this slide's title. This step will not be mentioned in future exercises. | |