Forum Discussion
Helper IIError trying to add a custom column
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.
Microsoft EmployeeHi 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
Community Champion
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.
MR2001Thanks, 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
Thanks Arify, problem is resolved now, I was just trying to format a DateTime field to include only Date information.