Forum Discussion

paris's avatar
paris
Helper V
7 years ago

Add a custom column

Hi All,

 

I would like to add a custom column in query.I have shipping date in sales table - unfortunately original formt is text and day and month is otherway around. 

 

So I wrote  the following Custom column fomular like I did in table .. but it does not work in query. 

"Expression.Error:The name'date' wasn't recognized. Make sure it'spelled correctly."

 

=date(year([Date]),DAY([Date]),MONTH([Date]))

 

What it went wrong?

 

All the best,

LoveParis

 

Need Help

5 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    "day and month is otherway around" Can you show us a date exactly please?

    • paris's avatar
      paris
      Helper V
      7 years ago

      Hi HotChilli,

       

      Thank you for your message. It looks like this. The original data is stored as text and when I try to convert to date by Data Type, I get 6 Jan 2019 while it is supposed to be 1 Jun 2019. I want to add new column and amend it in query. 

       

       

      All the best,

      LoveParis

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        7 years ago

        Hi paris , 

        To try to match your issue, I created a table in Excel with date as a text field.

        Then used PQ to bring it into PBI and it was brought in as a date field and formatted correctly.

        So, I changed it back to a text field in the query.

        And then changed it back to a date field.  It formatted correctly.

        It seems to be working as expected...can you provide us more detail?

        Thanks,

        Nathaniel_C