cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
great_AS Member
Member

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.

 

dvdsdsf.pngsdsvv.png

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Date format error

@great_AS

 

Please check if you turn on the "Auto detect column type" and set a proper locale.

 

123.PNG

 

 

14.PNG

 

See: Flawless Date Conversion in Power Query

 

Regards,

6 REPLIES 6
Super User
Super User

Re: Date format error

HI @great_AS,

 

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.

 

Untitled1.png

 

Any question please tell,

 

Regards

MFelix

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
Super User
Super User

Re: Date format error

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.

Specializing in Power Query Formula Language (M)
great_AS Member
Member

Re: Date format error

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?

Super User
Super User

Re: Date format error

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.

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: Date format error

Hello @great_AS,

 

I was looking at you images and noticed that all of the information as the date of 12 January except the first two, are you sure there isn't a problem in the dates or as @MarcelBeug refered to regional settings formats?

 

 

Regards

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Moderator v-sihou-msft
Moderator

Re: Date format error

@great_AS

 

Please check if you turn on the "Auto detect column type" and set a proper locale.

 

123.PNG

 

 

14.PNG

 

See: Flawless Date Conversion in Power Query

 

Regards,