Power BI Auto Transformation of Date Issue

[Jierenjoel]

Hi,

I got a question about how to resolve the transformation of the date.

Context: Enquiry Date is type "text", Enquiry_Date_Transformed is just "Enquiry Date" that is type "Date"

For some reason part of the data is formatted differently from the other rows. The correct format should be "dd-mm-yyyy" but I have no idea how to resolve this auto-detection and transformation issue.

　

Joel

[TimoRiikonen · Accepted solution]

Try to enforce the locale to your liking, here I have changed it to Finnish for Datetime field:

#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Local timestamp without offset", type datetime}}, "fi-FI"),

[JirkaZ]

IMHO you'll have to change your regional settings:

Go to Options and Settings -> Options -> Current File section -> Regional Settings and change to the appropriate locale.

[Jierenjoel]

I changed all regional settings and refreshed the datasource but it didn't change.

I resolved the issue by splitting the date into 3 seperate substring columns (day, month, year) and created a new date column with those components. However, it is an inelegant and burdensome method.

I just don't understand how half the data of a column can be automatically detected as "mm/dd/yyyy" and the other half of the same column as "dd/mm/yyyy". In addition, I cannot find a way to modify this "automatic detection"

Joel

[JirkaZ]

Are the differences within the same table or different tables? 
Also the preferences should be set prior the data load ideally and of course turn-off the automatic data type detection and auto Date/Time tables etc.