Group By month

[mork]

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

[mondinelli]

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]

I figured thats what I would have to do, can you provide the formula for the calculated column?

@mondinelli

[mondinelli]

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-b437... as a reference. But it's not exaustive.

[mork]

@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.

[Anonymous]

@mork try this:

   Number.ToText(Date.Month([WeekStartDate])) & "-" & Number.ToText(Date.Year([WeekStartDate]))

[mork]

@Anonymous That worked but can I change it to date type?

[Anonymous]

@mork which is the error while trying the second way?

[mork]

It doesn't show an error, it just creates the column with the word error in every cell.

[Anonymous]

Mmm it's strange. First of all, you have problems with the "WeekStartDate", which is the format of that column? Because in my example dataset I've no problems.

With the formula I've posted I get something like this : 1-2016

You can also try to create two separate custom column, one with Date.Month() and one with Date.Year() and then merge them together.

[mork]

The formula you provided worked. I just hadn't noticed that post and I was replying to your previous one about the error I was recieving. The WeekStartDate is type date

[mork]

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]

Hi @mork,

   I think you're searching this:  monthYear = MONTH(table[WeekStartDate]) & "-" & YEAR(table[WeekStartDate]).

[mork]

@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.

[Mi2n]

Hi @mork.. you can always look for the necessary M query formulas in the below link.

https://msdn.microsoft.com/en-us/library/mt211003.aspx

In your case, you would have to use the Date.Month() function.