Forum Discussion

bhavana_PBI's avatar
bhavana_PBI
New Member
8 years ago
Solved

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 ...
Dashboards
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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 

bizbi's avatar
bizbi
Icon for Advocate I rankAdvocate I
8 years ago

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)