Date table (Pivot, unpivot?)

[Scaffnull]

Hi everyone,

I have a question about a date table where I'd like to format so I can get total count of people and total hours per date. 
I got handled (picture, and filter the peoples names). So the week column continues until present week.

I wonder if anyone has a solution to fix dates for this. So example week 22 monday (May 31st)  the total count of name is 8 and total hours 59. I've tried to invoke a date table, but it doesn't recognize that monday week 22 is that date and so on.

Thanks for any sort of help.

[edhans · Accepted solution]

You need a date table, and your weeks need to have year indicators. Otherwise once you get 2 or more years, week 22 will be in different years.

When I get source data that only has info like this, adding the year is pretty easy. As for the date, you can use this method. It returns a table like this, that you can now create a relationship with a date table, and do your aggregations.

I did this in Excel, so Open up Power Query and look at the Table query.

Here is my file.

[Scaffnull]

@edhans Thank you for your reply. It worked, I created an empty query and imported a date table there in the advanced query, made the connection to the data table. I thought date for date was necessary but just kept it simple and displayed everything by weeks 🙂 

[edhans]

Great @Scaffnull - glad I was able to assist.