Power Query: Unpivot + Unstack 2 tabluar Tables into List

[Mathexcel]

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
[TABLE="width: 901"]
<colgroup><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Group[/TD]
[TD]Type[/TD]
[TD]Item[/TD]
[TD]01/01/2020[/TD]
[TD]02/01/2020[/TD]
[TD]03/01/2020[/TD]
[TD]04/01/2020[/TD]
[TD]05/01/2020[/TD]
[TD]06/01/2020[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Occupancy[/TD]
[TD]15.38%[/TD]
[TD]10.26%[/TD]
[TD]7.69%[/TD]
[TD]7.69%[/TD]
[TD]10.26%[/TD]
[TD]12.82%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Direct occupancy[/TD]
[TD]15.38%[/TD]
[TD]10.26%[/TD]
[TD]7.69%[/TD]
[TD]7.69%[/TD]
[TD]10.26%[/TD]
[TD]12.82%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Available[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Occupied[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Directly occupied[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Out of order[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Night revenue[/TD]
[TD]£1 086.21[/TD]
[TD]£713.15[/TD]
[TD]£532.70[/TD]
[TD]£481.28[/TD]
[TD]£575.61[/TD]
[TD]£775.77[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Revenue per available[/TD]
[TD]£27.85[/TD]
[TD]£18.29[/TD]
[TD]£13.66[/TD]
[TD]£12.34[/TD]
[TD]£14.76[/TD]
[TD]£19.89[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Average night rate[/TD]
[TD]£181.04[/TD]
[TD]£178.29[/TD]
[TD]£177.57[/TD]
[TD]£160.43[/TD]
[TD]£143.90[/TD]
[TD]£155.15[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Direct night revenue[/TD]
[TD]£1 086.21[/TD]
[TD]£713.15[/TD]
[TD]£532.70[/TD]
[TD]£481.28[/TD]
[TD]£575.61[/TD]
[TD]£775.77[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Customers[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Occupancy[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Direct occupancy[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Available[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Occupied[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Directly occupied[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Out of order[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Revenue per available[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Average night rate[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Direct night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Customers[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Occupancy[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Direct occupancy[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Available[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Occupied[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Directly occupied[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Out of order[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Revenue per available[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Average night rate[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Direct night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Site[/TD]
[TD]Customers[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

File 2
[TABLE="width: 901"]
<colgroup><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Group[/TD]
[TD]Type[/TD]
[TD]Item[/TD]
[TD]01/01/2019[/TD]
[TD]02/01/2019[/TD]
[TD]03/01/2019[/TD]
[TD]04/01/2019[/TD]
[TD]05/01/2019[/TD]
[TD]06/01/2019[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Occupancy[/TD]
[TD]69.23%[/TD]
[TD]69.23%[/TD]
[TD]61.54%[/TD]
[TD]69.23%[/TD]
[TD]66.67%[/TD]
[TD]43.59%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Direct occupancy[/TD]
[TD]69.23%[/TD]
[TD]69.23%[/TD]
[TD]61.54%[/TD]
[TD]69.23%[/TD]
[TD]66.67%[/TD]
[TD]43.59%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Available[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Occupied[/TD]
[TD]27[/TD]
[TD]27[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Directly occupied[/TD]
[TD]27[/TD]
[TD]27[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Out of order[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Night revenue[/TD]
[TD]£4 640.27[/TD]
[TD]£3 707.03[/TD]
[TD]£3 115.38[/TD]
[TD]£3 524.25[/TD]
[TD]£3 038.65[/TD]
[TD]£2 028.40[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Revenue per available[/TD]
[TD]£118.98[/TD]
[TD]£95.05[/TD]
[TD]£79.88[/TD]
[TD]£90.37[/TD]
[TD]£77.91[/TD]
[TD]£52.01[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Average night rate[/TD]
[TD]£171.86[/TD]
[TD]£137.30[/TD]
[TD]£129.81[/TD]
[TD]£130.53[/TD]
[TD]£116.87[/TD]
[TD]£119.32[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Direct night revenue[/TD]
[TD]£4 640.27[/TD]
[TD]£3 707.03[/TD]
[TD]£3 115.38[/TD]
[TD]£3 524.25[/TD]
[TD]£3 038.65[/TD]
[TD]£2 028.40[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Room[/TD]
[TD]Customers[/TD]
[TD]54[/TD]
[TD]49[/TD]
[TD]45[/TD]
[TD]51[/TD]
[TD]49[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Occupancy[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Direct occupancy[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Available[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Occupied[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Directly occupied[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Out of order[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Revenue per available[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Average night rate[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Direct night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Apartment[/TD]
[TD]Customers[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Occupancy[/TD]
[TD]6.67%[/TD]
[TD]6.67%[/TD]
[TD]13.33%[/TD]
[TD]13.33%[/TD]
[TD]6.67%[/TD]
[TD]13.33%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Direct occupancy[/TD]
[TD]6.67%[/TD]
[TD]6.67%[/TD]
[TD]13.33%[/TD]
[TD]13.33%[/TD]
[TD]6.67%[/TD]
[TD]13.33%[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Available[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Occupied[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Directly occupied[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Out of order[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Revenue per available[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Average night rate[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Direct night revenue[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]Stay[/TD]
[TD]Suite[/TD]
[TD]Customers[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[peter789]

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

 

[Mathexcel]

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?

I appreciate your time.
Best,
Matt

 

[peter789]

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