Forum Discussion

Icon for Helper V rank

Helper V

10 years ago

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# WeekSt...

Icon for Advocate I rank

Advocate I

10 years ago

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.

mork
Helper V
10 years ago
I figured thats what I would have to do, can you provide the formula for the calculated column?
mondinelli
- mondinelli
  Advocate I
  10 years ago
  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.
  - mork
    Helper V
    10 years ago
    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
  Helper V
  10 years ago
  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".
  - Anonymous
    Not applicable
    10 years ago
    Hi mork,
    
    I think you're searching this: monthYear = MONTH(table[WeekStartDate]) & "-" & YEAR(table[WeekStartDate]).

Related content