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
adrien5555
Helper II
Helper II

How to deal with errors on dates?

Hi all,

 

This date is in the format dd/mm/yyyy

 

How is it that it ends up with errrors when switching the data type from 'text' to 'date' ?

How to fix it?

 

Untitled picture.png

 

thx for helping!

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@adrien5555 Your Locale Setting must not be US - while your data source is set up to the US date format.

Look at the picture - I changed my default to UK and got the same error as you!

so you should  probably split by delimiter and then re-assemble in the order DD/MM/YYYY

 

Date Errors3.png

 

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

@adrien5555

 

not sure the 2nd part of value considered as Month as i see any values has >12 error'd

 

can you try with parse tool under transform 

Parse.PNG

 

@Anonymous : already tried the parse => same errors

Sean
Community Champion
Community Champion

@adrien5555 Your Locale Setting must not be US - while your data source is set up to the US date format.

Look at the picture - I changed my default to UK and got the same error as you!

so you should  probably split by delimiter and then re-assemble in the order DD/MM/YYYY

 

Date Errors3.png

 

Thanks. It worked for me fine, without doing additional Dax

Changing the local language option within PBI made it work.

 

I had to set it again from text to date, though.

 

Thanks!

Sean
Community Champion
Community Champion

Yes it does look like those are giving the errors!

 

I can't duplicate this tough... Added charachters, spaces before dates, formatted as Text still Query Editor recognizes immediately all dates as dates

 

Date Errors2.png

It comes from a Google Spreadsheet.

there are no spaces before or after dates.... 

 

Thx for all your answers!!

fchukoskie
Advocate I
Advocate I

I could be wrong, but that looks like you're translating from mm/dd/yyyy instead of dd/mm/yyyy to me.

@adrien5555 Are there additional spaces in the values that are throwing errors?..


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Also what is your data source?

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.