Dates in CSV Files

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
I've got a number of csv files organised in a hierarchy of folders. The first field in each record in each file is a date in the form yyyymmdd. There is no field name row at the beginning of the csv files, the data starts with record 1. No problem so far.
If I rename the first column from 'Column1' to 'Date' and then change the data type of the column to Date I get 'error' in that field on each record. If I change the data type to Date and then rename the column it works fine.
It's not a problem but being a bit of a perfectionist I like to be able to explain what's happening, particularly when I have to hand this over to someone who isn't familiar with the Force.
 
Following my normal practise of answereing my own questions I've just noticed something.
When you import data from a file and you go in to the Query Editor window you get the list of files with the first column showing Binary as normal. When you click on the downward arrows Power Query adds three steps to your data. 1. Combined Binaries (no surprise there) 2. Imported CSV (again no surprise) 3. Changed Type.

Why has it done that? What it's done is changed that first column to a whole number which then won't convert to a date. If you delete that step and then add it yourself, using the right data type of Date it's happy with it. Hey-ho. Now I can explain it to my user and when he asks me why it does that I'll do what my mother used to do which was to shrug her shoulders and say 'because'.
 
It's trying to be helpful. Why it would pick number, not date, though... that's odd.

Fwiw, I would suggest you avoid using Change Type --> Date, and instead pick Change Type --> Using Locale --> Date. That way you get to pick the source data format, and don't have to worry about what date format any users chooses in their Windows control panel. (That's what Date defaults too.)
 
Back
Top