Hi Mr Excel Community,
I would like to unstack this data using Power Query in order to have a clean list to pivot.
Desired situation > To have a table with the following headers: Market, Items, Years, Month, Values.
Change vs prior Year will be filtered out of the data sample.
I know that I need to merge, transpose, unpivot, unmerge and pivot but does anyone have an idea how to split the Items colums into Years & Items?
Data Sample:
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Many thanks in advance for your precious time!
Best ,
Matt
I would like to unstack this data using Power Query in order to have a clean list to pivot.
Desired situation > To have a table with the following headers: Market, Items, Years, Month, Values.
Change vs prior Year will be filtered out of the data sample.
I know that I need to merge, transpose, unpivot, unmerge and pivot but does anyone have an idea how to split the Items colums into Years & Items?
Data Sample:
Market | Items | Column2 | Column3 |
England | Occupancy (%) | Jan | Feb |
England | 2016 | 60 | 70 |
England | 2017 | 60 | 70 |
England | 2018 | 61 | 71 |
England | 2019 | 65 | 73 |
England | Change vs prior year (%) | 0.394330743 | 0.063125438 |
England | Average Daily Rate | Jan | Feb |
England | 2016 | 70 | 90 |
England | 2017 | 80 | 86 |
England | 2018 | 83 | 86 |
England | 2019 | 90 | 87 |
England | Change vs prior year (%) | 1.690275579 | -0.105899308 |
England | RevPAR | Jan | Feb |
England | 2016 | 50 | 60 |
England | 2017 | 56 | 62 |
England | 2018 | 56 | 61 |
England | 2019 | 55 | 53 |
England | Change vs prior year (%) | 2.091271599 | -0.04284072 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Many thanks in advance for your precious time!
Best ,
Matt