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

[bhavana_PBI]

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. 

[sentient]

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 · Accepted solution]

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 

[bhavana_PBI]

Hi @v-qiuyu-msft,

Thanks its working for me. 

[bizbi]

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)