Power Query: Unpivot + Unstack 2 tabluar Tables into List

Mathexcel

New Member
Joined
Jun 22, 2017
Messages
36
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
GroupTypeItem01/01/202002/01/202003/01/202004/01/202005/01/202006/01/2020
StayRoomOccupancy15.38%10.26%7.69%7.69%10.26%12.82%
StayRoomDirect occupancy15.38%10.26%7.69%7.69%10.26%12.82%
StayRoomAvailable393939393939
StayRoomOccupied643345
StayRoomDirectly occupied643345
StayRoomOut of order000000
StayRoomNight revenue£1 086.21£713.15£532.70£481.28£575.61£775.77
StayRoomRevenue per available£27.85£18.29£13.66£12.34£14.76£19.89
StayRoomAverage night rate£181.04£178.29£177.57£160.43£143.90£155.15
StayRoomDirect night revenue£1 086.21£713.15£532.70£481.28£575.61£775.77
StayRoomCustomers12866810
StayApartmentOccupancy0.00%0.00%0.00%0.00%0.00%0.00%
StayApartmentDirect occupancy0.00%0.00%0.00%0.00%0.00%0.00%
StayApartmentAvailable111111
StayApartmentOccupied000000
StayApartmentDirectly occupied000000
StayApartmentOut of order000000
StayApartmentNight revenue£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentRevenue per available£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentAverage night rate£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentDirect night revenue£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentCustomers000000
StaySiteOccupancy0.00%0.00%0.00%0.00%0.00%0.00%
StaySiteDirect occupancy0.00%0.00%0.00%0.00%0.00%0.00%
StaySiteAvailable111111
StaySiteOccupied000000
StaySiteDirectly occupied000000
StaySiteOut of order000000
StaySiteNight revenue£0.00£0.00£0.00£0.00£0.00£0.00
StaySiteRevenue per available£0.00£0.00£0.00£0.00£0.00£0.00
StaySiteAverage night rate£0.00£0.00£0.00£0.00£0.00£0.00
StaySiteDirect night revenue£0.00£0.00£0.00£0.00£0.00£0.00
StaySiteCustomers000000

<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>

File 2
GroupTypeItem01/01/201902/01/201903/01/201904/01/201905/01/201906/01/2019
StayRoomOccupancy69.23%69.23%61.54%69.23%66.67%43.59%
StayRoomDirect occupancy69.23%69.23%61.54%69.23%66.67%43.59%
StayRoomAvailable393939393939
StayRoomOccupied272724272617
StayRoomDirectly occupied272724272617
StayRoomOut of order000000
StayRoomNight revenue£4 640.27£3 707.03£3 115.38£3 524.25£3 038.65£2 028.40
StayRoomRevenue per available£118.98£95.05£79.88£90.37£77.91£52.01
StayRoomAverage night rate£171.86£137.30£129.81£130.53£116.87£119.32
StayRoomDirect night revenue£4 640.27£3 707.03£3 115.38£3 524.25£3 038.65£2 028.40
StayRoomCustomers544945514931
StayApartmentOccupancy0.00%0.00%0.00%0.00%0.00%0.00%
StayApartmentDirect occupancy0.00%0.00%0.00%0.00%0.00%0.00%
StayApartmentAvailable111111
StayApartmentOccupied000000
StayApartmentDirectly occupied000000
StayApartmentOut of order000000
StayApartmentNight revenue£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentRevenue per available£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentAverage night rate£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentDirect night revenue£0.00£0.00£0.00£0.00£0.00£0.00
StayApartmentCustomers000000
StaySuiteOccupancy6.67%6.67%13.33%13.33%6.67%13.33%
StaySuiteDirect occupancy6.67%6.67%13.33%13.33%6.67%13.33%
StaySuiteAvailable151515151515
StaySuiteOccupied112212
StaySuiteDirectly occupied112212
StaySuiteOut of order000000
StaySuiteNight revenue£0.00£0.00£0.00£0.00£0.00£0.00
StaySuiteRevenue per available£0.00£0.00£0.00£0.00£0.00£0.00
StaySuiteAverage night rate£0.00£0.00£0.00£0.00£0.00£0.00
StaySuiteDirect night revenue£0.00£0.00£0.00£0.00£0.00£0.00
StaySuiteCustomers224414

<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
The way I tackle these kinds of patterns is to merge the first three columns; delimit with a unique txt string. I often use##. You can then unpivot the date columns. Next split the merged column back into its parts. Reorder and rename etc.. Repeat for second table and append to the first. (if more than two tables are likely to be involved consider wrapping the code in a function)

Peter
 
Upvote 0
Hi Peter,

Thank you for your reply.

I managed to run the Query but only on one table at a time....

The assignment should only consider 2 files dropped everyday in a folder. Idealy, as you mentionned, I would like the code to execute the Query on every file, and then append them.

Could you please clarify the "Repeat for second table and append to the first. (if more than two tables are likely to be involved consider wrapping the code in a function)"?

I never coded into Power Query but understand the looping and function in Python. Do you have any pointers regarding the wrapping part or how to "repeat" the action to the second table? :rolleyes:

I appreciate your time.
Best,
Matt
 
Last edited:
Upvote 0
Sorry for the delay in replying I've been away for the weekend.
To keep it simple don't worry about using a function yet.
You could follow these steps.
Create query linked to first data table and transform the data as above.
Duplicate this query but edit the path to the source to get the data from the second table. Make sure the headers are named the same.
Then append the two queries.
Peter
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top