Add a column / custom column fomular

[paris]

Hi All,

My original data is text format and month and date are otherway around due to wrong regional setting.

It is supposed to be 1 Jun 2019 but shown as 6 Jan 2019 when I change format by using data type. 

I want to marge two tables later so I want to amend it by using custom column fomula in query. 

The folllowing dax works in data but not query. Can anyone help?

=DATE(YEAR([Date]),DAY([Date]),MONTH([Date]))

All the best,

LoveParis

[AlB]

Hi @paris 

Have you tried changing the file option for loading data and choosing the locale you need? Go to 

 Options and Settings->Options-> (Current File) Regional settings 

and in "Locale for import" choose an appropriate option.  English(United States) would probably do it, from what you describe. The selection of the locale need to happen before you change the type of the column to Date

[paris]

ABI
Thank you for your message.
I am trying to avoid changing region by setting as it breaks other calculations.
So I was wondering if I can do it in a query.

All the best
LoveParis

[Omega]

Try creating a new column using "Columns from Example". Try the below steps: 

1. Select the date column
2. On the Ribbon, click Add Column
3. Select Column From Examples and the choose from selection
4. A new column will appear, enter the expected values

🙂

[AlB · Accepted solution]

@paris 

Try  creating a custom column in Power query with the code: 

Date.FromText([Column1], "en-US")

and then change the type of the new column to Date. If required, delete the original "Column 1"

[paris]

Hi AIB,

Thank you very much for your advice.  It worked perfectly. 

All the best,

LoveParis