Forum Discussion

MR2001's avatar
MR2001
Icon for Helper II rankHelper II
10 years ago
Solved

Error trying to add a custom column

I am getting the following error when trying to add a custom column: "The name FORMAT wasn't recognized"

 

Please advise.

 

Thank you.

 

 

 

Need Help
  • kcantor's avatar
    kcantor
    10 years ago

    MR2001

    Instead of working in the query editor, have you tried adding a calculated column within the data model? I just used Format in that sense in the Data Model by adding the calculated column there as opposed to writing an "M" string in Query Editor. I used a simple Year-Week = [Year]&"-"&FORMAT([WeekNumber],"00") just to make sure FORMAT worked as desired in the datamodel.

    Hope this helps.

    As an added hint:

     I am new to M but I can tell you that all caps throws an error. Only capitilize the F in Format but I don't think Format works anyway.

    Not really helpful for this but the all caps thing might come in handy at some point in the future.

     

10 Replies

    • MR2001's avatar
      MR2001
      Icon for Helper II rankHelper II
      10 years ago

      Thank you Smoupre, I realize now that "M" and DAX are different.

      • kcantor's avatar
        kcantor
        Icon for Community Champion rankCommunity Champion
        10 years ago

        Fortunately, for those of us new to both DAX and M we usually get the option of which to use. I find DAX easier but am working to learn M. Its a process.

        Glad it works for you.

  • arify's avatar
    arify
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi MR2001, if you can't find what you need in there, can you describe what you want to do so we may be able to help?

    • kcantor's avatar
      kcantor
      Icon for Community Champion rankCommunity Champion
      10 years ago

      MR2001

      Instead of working in the query editor, have you tried adding a calculated column within the data model? I just used Format in that sense in the Data Model by adding the calculated column there as opposed to writing an "M" string in Query Editor. I used a simple Year-Week = [Year]&"-"&FORMAT([WeekNumber],"00") just to make sure FORMAT worked as desired in the datamodel.

      Hope this helps.

      As an added hint:

       I am new to M but I can tell you that all caps throws an error. Only capitilize the F in Format but I don't think Format works anyway.

      Not really helpful for this but the all caps thing might come in handy at some point in the future.

       

      • MR2001's avatar
        MR2001
        Icon for Helper II rankHelper II
        10 years ago

        Thanks, Kcantor, I added a custom column using FORMAT function and it worked. I also tried something similar using "M" language:

         

        Custom Date = Number.ToText(Date.Month([#"history#action_timestamp"])) & "/" & Number.ToText(Date.Year([#"history#action_timestamp"]))

         

        Thanks,

        M.R.

    • MR2001's avatar
      MR2001
      Icon for Helper II rankHelper II
      10 years ago

      Thanks Arify, problem is resolved now, I was just trying to format a DateTime field to include only Date information.