Problem in changing format from text to date

[OmarSaleh]

Hi, 

One of the tables I have is imported from an excel sheet with a column in Date/Time format as shown below. 

I need to convert it to Date in power Bi (not excel). The step I'm following are as follows:

1- Trim the colum and keeping the date only (ex. 14/01/2021)

2- Convert format from Text to Date 

however, when I do this I get an error because of the regional settings (Locale for import) set on US Time.

Anyone can advise please? 

Note: I tried to change the settings for the entire file from Options & Settings --> Options --> Regional Settings, and selected UK Time, but didn't success as after implementing the change I got the error message "loading blocked by failures with other queries" for all the other tables in the file. 

I would be grarful if anyone can help. 

[Fowmy]

@OmarSaleh 

As pointed out by @HotChilli , Choose English (United Kingdom)

[KNP]

Hi @OmarSaleh - try conversion using locale.

= Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-NZ")

[OmarSaleh]

Just found it by right clicking on the column --> change type instead of using the tools bar. Thank you @KNP! 

[OmarSaleh]

Hi @KNP , I don't have "using locale" option in the dropdown list. Any idea? 

[HotChilli · Accepted solution]

right-click on the column, pick 'change type'-> using locale.  I can't remember if you need to pick English(uk) or English(u.s.) but one of them will work

[OmarSaleh]

Thanks @HotChilli. It worked perfectly with English UK.