Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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-b437... 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 try this:
Number.ToText(Date.Month([WeekStartDate])) & "-" & Number.ToText(Date.Year([WeekStartDate]))
@Anonymous That worked but can I change it to date type?
It doesn't show an error, it just creates the column with the word error in every cell.
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.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |