Forum Discussion
Date table (Pivot, unpivot?)
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.
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.
3 Replies
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.
