Forum Discussion
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.
please tell me where I am going wrong.
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"))
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
4 Replies
bizbiAdvocate 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)
v-qiuyu-msftCommunity 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"))
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 YuIf you use the Power Query Editor. You have to use the M language functions
E.g.
Text.PadStart(Text.From([Month Name]),2,"0")
