Forum Discussion
Group By month
Hello all,
I have a table of my Resources work per week that I'm trying to group by month. How can I do that? Example table bellow.
ResourceName Week# WeekStartDate WorkInHours
resource 1 1 1/1/16 40
resource 1 2 8/1/16 32
resource 2 1 1/1/16 32
resource 2 2 8/1/16 40
.
.
.
.
etc.
I want to group By month and resource, That way I will have on row for each resource's and month's work. I want something similar to bellow:
ResourceName Month# MonthStartDate WorkInHours
resource 1 1 1/1/16 160
resource 1 2 1/2/16 152
resource 2 1 1/1/16 160
resource 2 2 1/2/16 152
14 Replies
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.
