Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am getting the following error when trying to add a custom column: "The name FORMAT wasn't recognized"
Please advise.
Thank you.
Solved! Go to Solution.
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.
Proud to be a Super User!
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?
Thanks Arify, problem is resolved now, I was just trying to format a DateTime field to include only Date information.
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.
Proud to be a Super User!
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.
Looks like you are trying to use a DAX function in Power Query "M" code.
https://msdn.microsoft.com/en-us/library/mt211003.aspx
Thank you Smoupre, I realize now that "M" and DAX are different.
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.
Proud to be a Super User!
@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
True that @Sean. they will probably add it as soon as I reach a rudimentary level.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |