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