dates all converting to American format - urgent help please

[andyor]

All of my dates for every report in my companys workspaces have converted to american date format all of a sudden, meaning all data is showing incorrect. Power BI desktop and service now both think 8th January 2023 is the 1st of August 2023 etc. 

I've tried everything obvious for past few hours...

[Martaspg]

Hi, you should check workspace configuration for dates-time, also if your data is coming from Sharepoint or SQL, Azure... what is the culture configuration in  the source, maybe there has been an update that have changed this.

Alternatively, in Power Query, for each date column you click on the format (at the top-left) and select "culture", that enables to indicate which is the day, month and year by selecting the country and language (you will be able to select USA and English). 

Let me know if you can solve it, if not I will try to send some screenshots. 

[andyor]

hi guys, unfortunately this just give an error for each date that couldn't be a real date in reverse. 

So a date like 01/03/2022 can work. But something like 01/22/2022 doesn't. 

It's a completely random thing the way this has started happening.

[andyor]

My source data has the format 'yyyy-mm-dd hh:mm:ss' direct from the database, and suddently became an issue today that power BI desktop imports this as m/dd/yyyy hh:mm:ss, i.e., randomly reversing the month and the day with american date format. Prior to today, it imported as d/mm/yyyy hh:mm:ss

[andyor]

Given it's text, this doesn't do anything. If I change the format to date or datetime, it will give an 'error' on any dates that fall outside the normal Australian datetime format in reverse. e.g. 10/20/2022 and 7/27/2019 both will give an error 

[andyor]

The source data is the same as always for the dates...but all of a sudden the dates import into desktop like this. 

I specifically import them as text as if I ever tried to import as date or datetime, I encountered this same issue of power BI thinking everything is american date format. 

[FreemanZ]

hi @andyor 

it seems the locale setting is improper. Try:

1) In PQ, right click the date column header, and then select Change Type > Using Locale.

2) In the Change Type with Locale dialog box, select a data type and locale.

3) Select OK.

https://learn.microsoft.com/en-us/power-query/data-types

https://support.microsoft.com/en-au/office/set-a-locale-or-region-for-data-power-query-d42b9390-1fff...

[Misso101]

This worked perfectly thank you!