Powerquery - Editor is forcing US Date format including for parameters when source data is in UK Date format

Question

My data is in UK date format, powerquery editor displays this as US date format, and when I load the table back in to excel, it displays it as UK date format. This is an issue because it forces me to write dates in US date format in to parameters. This is what I've tried so far:

1. Setting Locale to UK date (as source data is in UK date format)
2. Setting Regional settings to UK
3. Splitting the date column (which is displayed in US format), reordering the month and day columns, and the merging the day, month, year column with a "/" separator. When I then set the data format to "date", it just changes the date back around to US date format
4. Checked windows settings on my laptop which are set to UK

Is there a setting in powerquery that I'm missing? I'd like to be able to enter a UK date format in to parameters as I won't be the only one using the spreadsheet and currently it will be very prone to errors especially for the first 12 days of each month.

Thankyou!

Answer 1

You can change type "Using locale" and set UK format.

Right click on column name > Change type > Using locale > pick type and format

Corresponding code would be:

= Table.TransformColumnTypes(#"Sorted Rows", {{"date", type date}}, "en-GB")