Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bhavana_PBI
New Member

Expression error: The name 'FORMAT' wasn't recognized. Make sure it's spelled correctly

This is my second day to Power BI, I am very new.

I want to convert month name to number and I am using add custome column feature of power bi. and it gives me above error.

this is what i have written : FORMAT([Month Name],00), I am using format function.

Capture.PNG

please tell me where I am going wrong. 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @bhavana_PBI,

 

From the screenshot, you are creating a custom column in Query Editor, it will use Power Query. But what you write is DAX. If you want to add a custom column to return month value in Query Editor, please write a formula below: 

 

=Date.Month(Date.FromText([Month Name] & " 15," & "2018"))

 

q6.PNG

 

Besides, the formula posted by @bizbi are DAX, you can refer to this tutorial to do it: https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
sentient
Regular Visitor

If you use the Power Query Editor. You have to use the M language functions

 

E.g.

Text.PadStart(Text.From([Month Name]),2,"0")

v-qiuyu-msft
Community Support
Community Support

Hi @bhavana_PBI,

 

From the screenshot, you are creating a custom column in Query Editor, it will use Power Query. But what you write is DAX. If you want to add a custom column to return month value in Query Editor, please write a formula below: 

 

=Date.Month(Date.FromText([Month Name] & " 15," & "2018"))

 

q6.PNG

 

Besides, the formula posted by @bizbi are DAX, you can refer to this tutorial to do it: https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft,

 

 

Thanks its working for me. Smiley Happy

bizbi
Advocate I
Advocate I

FORMAT is a DAX function and Query Editor works in M language. Try closing the Query Editor and adding a new calculated column in the data view in Power BI: Try either of the following:

 

1. If the date is available at record level: 

Month Number = MONTH('Table'[Date])

 

If Month Number is needed in XX format (i.e. as 01, 02, 03,...,12), use the following:

Month Number = FORMAT(MONTH('Table'[Date]),"00")

 

2. If date is not available, try the SWITCH function:

Month Number = SWITCH('Table'[MonthName],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.