How to deal with errors on dates?

[adrien5555]

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?

thx for helping!

[Anonymous]

@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 

[adrien5555]

@Anonymous : already tried the parse => same errors

[Sean · Accepted solution]

@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

[Romeshpsl]

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

[adrien5555]

Changing the local language option within PBI made it work.

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

Thanks!

[Sean]

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

[adrien5555]

It comes from a Google Spreadsheet.

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

Thx for all your answers!!

[fchukoskie]

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

[Seth_C_Bauer]

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

[Sean]

Also what is your data source?