cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
paris
Helper V
Helper V

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

 

5 REPLIES 5
HotChilli
Super User
Super User

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

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. 

 

Annotation 2019-08-15 100053.png

 

All the best,

LoveParis

Hi @paris , 

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

Date1.PNG

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

Date2.PNG

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

Date3.PNG

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

date4.PNG

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

Thanks,

@Nathaniel_C 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @paris ,

You might also check your regional settings:

Choose the locale for importing data into Power BI Desktop
  1. In Power BI Desktop, go to File > Options and settings > Options.
  2. Under Current file, select Regional Settings.
  3. In the Locale for import box, select a different locale.
  4. date5.PNG

If this solves your issue, please consider marking this as the solution.

@Nathaniel_C 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors