I have a table which has the months across the table for a calendar year (31/07/2016 to 30/06/2017). I have the same dates multiple times therefore the table has added a number to the end of the date eg 30/09/20162. How can I unpivot the dates for each year so that I can have a column for each year.
I have attached a file to show the table and what I need to get it to look like.
This one I ventured into as if it were a riddle: had to stop and think a couple of times about where the data needed to end up.
In PQ, after selecting just the Name column, there was an especially helpful starter transformation option: Unpivot -> Unpivot Other Columns.
Everything then fell into place: split, replace, pivot, done.
Interesting to me was that Power Query, immediately upon splitting, figured out by itself that one of the new columns would be a date. Cool!
The attached workbook is in Excel 2016 format, but since it uses Power Query without Power Pivot, I think any modern version of Excel with PQ can access the Query Editor. If not, let me know and I will attach the M code in the next comment.