Hi all,
I need help on a data transformation assignment in Power Query.
The goal is to unpivot and unstack data from two tabluar data sets and transform them into a list.
Data Source = 2 .xlsx Files in Folder (please see data example below)
Data Type = Tablular format with yearly dates on top. One file per year.
The challenge:
1) How to combine and unpivot two data sets and preserve the dates.
2) How to unstack the table from the [item] column
Desired list Header = Dates, Group, Type, Occupancy, Direct Occpancy, ..., Direct night Revenue, Customers
Data example
<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
File 2
<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
I need help on a data transformation assignment in Power Query.
The goal is to unpivot and unstack data from two tabluar data sets and transform them into a list.
Data Source = 2 .xlsx Files in Folder (please see data example below)
Data Type = Tablular format with yearly dates on top. One file per year.
The challenge:
1) How to combine and unpivot two data sets and preserve the dates.
2) How to unstack the table from the [item] column
Desired list Header = Dates, Group, Type, Occupancy, Direct Occpancy, ..., Direct night Revenue, Customers
Data example
Group | Type | Item | 01/01/2020 | 02/01/2020 | 03/01/2020 | 04/01/2020 | 05/01/2020 | 06/01/2020 |
Stay | Room | Occupancy | 15.38% | 10.26% | 7.69% | 7.69% | 10.26% | 12.82% |
Stay | Room | Direct occupancy | 15.38% | 10.26% | 7.69% | 7.69% | 10.26% | 12.82% |
Stay | Room | Available | 39 | 39 | 39 | 39 | 39 | 39 |
Stay | Room | Occupied | 6 | 4 | 3 | 3 | 4 | 5 |
Stay | Room | Directly occupied | 6 | 4 | 3 | 3 | 4 | 5 |
Stay | Room | Out of order | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Room | Night revenue | £1 086.21 | £713.15 | £532.70 | £481.28 | £575.61 | £775.77 |
Stay | Room | Revenue per available | £27.85 | £18.29 | £13.66 | £12.34 | £14.76 | £19.89 |
Stay | Room | Average night rate | £181.04 | £178.29 | £177.57 | £160.43 | £143.90 | £155.15 |
Stay | Room | Direct night revenue | £1 086.21 | £713.15 | £532.70 | £481.28 | £575.61 | £775.77 |
Stay | Room | Customers | 12 | 8 | 6 | 6 | 8 | 10 |
Stay | Apartment | Occupancy | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Stay | Apartment | Direct occupancy | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Stay | Apartment | Available | 1 | 1 | 1 | 1 | 1 | 1 |
Stay | Apartment | Occupied | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Apartment | Directly occupied | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Apartment | Out of order | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Apartment | Night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Revenue per available | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Average night rate | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Direct night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Customers | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Site | Occupancy | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Stay | Site | Direct occupancy | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Stay | Site | Available | 1 | 1 | 1 | 1 | 1 | 1 |
Stay | Site | Occupied | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Site | Directly occupied | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Site | Out of order | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Site | Night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Site | Revenue per available | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Site | Average night rate | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Site | Direct night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Site | Customers | 0 | 0 | 0 | 0 | 0 | 0 |
<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
File 2
Group | Type | Item | 01/01/2019 | 02/01/2019 | 03/01/2019 | 04/01/2019 | 05/01/2019 | 06/01/2019 |
Stay | Room | Occupancy | 69.23% | 69.23% | 61.54% | 69.23% | 66.67% | 43.59% |
Stay | Room | Direct occupancy | 69.23% | 69.23% | 61.54% | 69.23% | 66.67% | 43.59% |
Stay | Room | Available | 39 | 39 | 39 | 39 | 39 | 39 |
Stay | Room | Occupied | 27 | 27 | 24 | 27 | 26 | 17 |
Stay | Room | Directly occupied | 27 | 27 | 24 | 27 | 26 | 17 |
Stay | Room | Out of order | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Room | Night revenue | £4 640.27 | £3 707.03 | £3 115.38 | £3 524.25 | £3 038.65 | £2 028.40 |
Stay | Room | Revenue per available | £118.98 | £95.05 | £79.88 | £90.37 | £77.91 | £52.01 |
Stay | Room | Average night rate | £171.86 | £137.30 | £129.81 | £130.53 | £116.87 | £119.32 |
Stay | Room | Direct night revenue | £4 640.27 | £3 707.03 | £3 115.38 | £3 524.25 | £3 038.65 | £2 028.40 |
Stay | Room | Customers | 54 | 49 | 45 | 51 | 49 | 31 |
Stay | Apartment | Occupancy | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Stay | Apartment | Direct occupancy | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Stay | Apartment | Available | 1 | 1 | 1 | 1 | 1 | 1 |
Stay | Apartment | Occupied | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Apartment | Directly occupied | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Apartment | Out of order | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Apartment | Night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Revenue per available | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Average night rate | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Direct night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Apartment | Customers | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Suite | Occupancy | 6.67% | 6.67% | 13.33% | 13.33% | 6.67% | 13.33% |
Stay | Suite | Direct occupancy | 6.67% | 6.67% | 13.33% | 13.33% | 6.67% | 13.33% |
Stay | Suite | Available | 15 | 15 | 15 | 15 | 15 | 15 |
Stay | Suite | Occupied | 1 | 1 | 2 | 2 | 1 | 2 |
Stay | Suite | Directly occupied | 1 | 1 | 2 | 2 | 1 | 2 |
Stay | Suite | Out of order | 0 | 0 | 0 | 0 | 0 | 0 |
Stay | Suite | Night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Suite | Revenue per available | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Suite | Average night rate | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Suite | Direct night revenue | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 | £0.00 |
Stay | Suite | Customers | 2 | 2 | 4 | 4 | 1 | 4 |
<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>