Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous

 

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,

View solution in original post

7 REPLIES 7
ashwaghosh
Frequent Visitor

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.

MarcelBeug
Community Champion
Community Champion

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)
MFelix
Super User
Super User

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.

 

Untitled1.png

 

Any question please tell,

 

Regards

MFelix

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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?

@Anonymous

 

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,

Hello @Anonymous,

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.