Forum Discussion
Date format error
Hello everybody,
when I update my input file PBI returns me an errore about Date Format. The problem is that it returns me that error only on a specific value, whereas it's able to convert all the other. I checked the source file and the format is the same for all.
Anybody knows how to solve it?
Thanks in advance.
Anonymous
Please check if you turn on the "Auto detect column type" and set a proper locale.
See: Flawless Date Conversion in Power Query
Regards,
7 Replies
Probably your current culture expects dates in mm-dd-yy format instead of dd-mm-yy.
In that case all other dates may be misinterpreted a well, e.g. Jan-12, 2016 is in fact Dec 1, 2016.
The solution would be to add an additional argument at the end of your code with the culture code (or Locale), that suits your data.
An example is the code for The Netherlands (Dutch): "nl-NL".
You can also find this functionality when you click the data type button at the top left of your column header or if you right click the column header and choose " Change Type" using Locale.
HI Anonymous,
Sometimes I have this issue and what happens is that the specific cell as one space or some other carachteristic like that in the end doesn't allow to make the conversion, although in the data source everything is OK. Try to copy the value and compare all the caraters with another value that converts ok.
To check this please add a custom column to your output and then place the following formula
= try[Date]
This formula will "try" to do the formating and return you the the detail information about the error please check the print below, probably this will not give much more information than you already have but it's worth a shot, and as I said also check if you don't have any additional carachter in that specific record.
Any question please tell,
Regards
MFelix
Hello MFelix,
thx for your reply. I applied the steps you suggested and I got the same as you. Here come my question, hos do I fix this?
If you have leading or trailing spaces, select the step before types are changed, right-click the column header - choose "Transform" - choose "Trim" (if it's not there choose "Text Transforms" first and then "Trim").
You may get a warning about inserting a step but just go ahead and click OK.
As of today latest solution available change type using Locale. There is the last option provided "Using Locale....".It seamlessly converts datatype based on selected locale. No more need to update the PC's regional setting.
