Error trying to add a custom column

[MR2001]

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

Please advise.

Thank you.

[arify]

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?

[MR2001]

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

[kcantor · Accepted solution]

@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]

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.

[Greg_Deckler]

Looks like you are trying to use a DAX function in Power Query "M" code.

https://msdn.microsoft.com/en-us/library/mt211003.aspx

[MR2001]

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

[kcantor]

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.

[Sean]

@kcantor same here I agree

But they can help us learn faster if they add intellisense in the Query Editor

so you get a little help when writing formulas there - best way to learn

[kcantor]

True that @Sean. they will probably add it as soon as I reach a rudimentary level.

[Sean]