Forum Discussion

paris's avatar
paris
Helper V
7 years ago
Solved

Add a column / custom column fomular

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

 

 

 

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    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"

     

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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's avatar
      paris
      Helper V
      7 years ago
      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's avatar
        Omega
        Impactful Individual
        7 years ago

        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

         

        :)