Forum Discussion
Group By month
in powerquery:
add a calculated column with the "month"-"year", like 12-2015,01-2016,02-2016 ecc...
group by that column and teh resource column and summarize other column as you like.
I figured thats what I would have to do, can you provide the formula for the calculated column?
Number.From(Date.Month(Date.From([DATACOLUMN])))+100*NumberFrom(Date.Year(Date.From([DATACOLUMN])))
or
Date.Month(Date.From([DATACOLUMN]))&"-"&Date.Year(Date.From([DATACOLUMN]))
mork take https://support.office.com/en-sg/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819 as a reference. But it's not exaustive.
mondinelli I tried both your ways and only the first worked but I don't want that format... I prefer the format of the second way that doesn't work.
mork which is the error while trying the second way?
I tried using the MONTH function like bellow:
=MONTH([WeekStartDate])
And I get the following error.
"The name 'MONTH' wasn't recognized. Make sure it's spelled correctly.
Also how can I add the YEAR function to that? I want a format that would look like "Month-Year".
Hi mork,
I think you're searching this: monthYear = MONTH(table[WeekStartDate]) & "-" & YEAR(table[WeekStartDate]).
Anonymous That should work but it doesn't. I get the same error.
What I'm doing is that in powerquery I select the "add column" tab and then I select "add custom column". A window pops up and I write the formula in the window. But the formula is in DAX language and not in M. Maybe that's the error? But I don't want to create a calculated column outside of powerquery because I want to group according to that column.