I am struggling to convert data and time from a csv file to the correct format in power query.
In my csv file the date and time is created as "24/05/21 06:07:30 AM" (24 May 2021) - being the correct format of "dd/mm/yy hh:mm:ss"
Once opened in power query it converts to type datetime and presents as "21/05/24 06:07:30 AM".
The problem is that the 21 is now taken as dd in stead of yy and the 24 as yy in stead of dd.
If I change the column format to locale Date/Time - English (South Africa), it does not change anything. Our locale settings for dates are dd/mm/yy.
Any suggestions please ?
Hi Kobus,
Welcome to our forum!
Did you remove the first changed type step before applying change type using locale? Once it's changed incorrectly, change type using locale won't work.
Mynda
Hi Mynda,
Thanks for the feedback. I have removed the initial changed type step. My current steps are:
let
Source = Csv.Document(File.Contents("M:Abattoir Kloktm1206_xi_clockingtimes - Copy.csv"),[Delimiter=",", Columns=26, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Column14", type datetime}}, "en-ZA")
in
#"Changed Type with Locale"
This still gives the same incorrect date result as previously mentioned.
I edited the date in the csv file to "24/05/2021 06:07:30 AM" and opened power query, removing the changed step and applying my locale setting (English South Africa). This then returns an error for the specific field:
"DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
24/05/2021 06:07:30AM"
I then played around with the locale settings and found the French(France) setting to give me the correct result.
French examples gives dd/mm/yyyy hh:mm:ss (yyyy and 24 hour format)
English (South Africa) gives dd/mm/yy hh:mm:ss PM (yy and 12 hour format)
Your thoughts ?
Not sure why the French settings are working. Perhaps you have that as your system settings?
Either way, don't worry too much about whether it's showing a 4 digit year and the time is in 24 or 12 hour format as you can change the formatting once you load it into Excel or Power BI. The underlying date-time serial number will be correct, and that's the main thing.
Mynda
Thanks so much for your time. I appreciate the assistance.
