Transpose columns and rows under the current year

[ashley1984]

Hi,

I'm using Power Query to clean and transpose a set of data from LY under the same weeks this year. You can see from the below that I have 12 weeks from LY with the same 12 weeks from TY with W/C a Monday

			Aug-2020				Sep-2020				Oct-2020				Aug-2021				Sep-2021				Oct-2021
Category 1	Category 4	Brand	w/c 10-Aug	w/c 17-Aug	w/c 24-Aug	w/c 31-Aug	w/c 07-Sep	w/c 14-Sep	w/c 21-Sep	w/c 28-Sep	w/c 05-Oct	w/c 12-Oct	w/c 19-Oct	w/c 26-Oct	w/c 09-Aug	w/c 16-Aug	w/c 23-Aug	w/c 30-Aug	w/c 06-Sep	w/c 13-Sep	w/c 20-Sep	w/c 27-Sep	w/c 04-Oct	w/c 11-Oct	w/c 18-Oct	w/c 25-Oct
Product 1	Ingredient 1	Brand A	Save £1.00														Save £x.00						Save £x.00
Product 2	Ingredient 2	Brand A							Save £x.00						Save £x.00					Save £x.00
Product 3	Ingredient 3	Brand A							Save £x.00								Save £x.00
Product 4	Ingredient 4	Brand B										Save £x.00			Save £x.00
Product 5	Ingredient 5	Brand B										Save £x.00			Save £x.00
Product 6	Ingredient 6	Brand B										Save £x.00								Save £x
Product 7	Ingredient 7	Brand B										Save £x.00					Save £x.00									Save £x
Product 8	Ingredient 8	Brand B										Save £x.00											Save £x.00
Product 9	Ingredient 9	Brand B										Save £x.00														Save £x
Product 10	Ingredient 10	Brand C										Save £x.00								Save £x						Save £x
Product 11	Ingredient 11	Brand C	Save £0.50												Save £x.00					Save £x
Product 12	Ingredient 12	Brand C	Save 25%												Save £x.00
Product 13	Ingredient 13	Brand C				Save £0.20						Save £x.00			Save £x.00
Product 14	Ingredient 14	Brand C										Save £x.00											Save £x
Product 15	Ingredient 15	Brand C																		Save £x
Product 16	Ingredient 16	Brand C													Save £x.00
Product 17	Ingredient 17	Brand C							Save £x.00						Save £x.00					Save £x
Product 18	Ingredient 18	Brand C																		Save £x
Product 19	Ingredient 19	Brand D										Save £x.00			Save £x.00
Product 20	Ingredient 20	Brand D										Save £x.00			Save £x.00					Save £x						Save £x
Product 21	Ingredient 21	Brand D										Save £x.00			Save £x.00
Product 22	Ingredient 22	Brand D										Save £x.00			Save £x.00
Product 23	Ingredient 23	Brand D																					Save £x
Product 24	Ingredient 24	Brand D																								Save £x
Product 25	Ingredient 25	Brand D																								Save £x
Product 26	Ingredient 26	Brand D															Save £x						Save £x
Product 27	Ingredient 27	Brand D													Save £x
Product 28	Ingredient 28	Brand D															Save £x
Product 29	Ingredient 29	Brand D															Save £x
Product 30	Ingredient 30	Brand D															Save £x
Product 31	Ingredient 31	Brand D																		Save £x
Product 32	Ingredient 32	Brand D																								Save £x
Product 33	Ingredient 33	Brand D															Save £x						Save £x

The output I am looking for is:

			Aug-2021				Sep-2021				Oct-2021
Category 1	Category 4	Brand	w/c 09-Aug	w/c 16-Aug	w/c 23-Aug	w/c 30-Aug	w/c 06-Sep	w/c 13-Sep	w/c 20-Sep	w/c 27-Sep	w/c 04-Oct	w/c 11-Oct	w/c 18-Oct	w/c 25-Oct
Product 1	Ingredient 1	Brand A			Save £x.00						Save £x.00
Product 2	Ingredient 2	Brand A	Save £x.00					Save £x.00
Product 3	Ingredient 3	Brand A			Save £x.00
Product 4	Ingredient 4	Brand B	Save £x.00
Product 5	Ingredient 5	Brand B	Save £x.00
Product 6	Ingredient 6	Brand B						Save £x
Product 7	Ingredient 7	Brand B			Save £x.00									Save £x
Product 8	Ingredient 8	Brand B									Save £x.00
Product 9	Ingredient 9	Brand B												Save £x
Product 10	Ingredient 10	Brand C						Save £x						Save £x
Product 11	Ingredient 11	Brand C	Save £x.00					Save £x
Product 12	Ingredient 12	Brand C	Save £x.00
Product 13	Ingredient 13	Brand C	Save £x.00
Product 14	Ingredient 14	Brand C									Save £x
Product 15	Ingredient 15	Brand C						Save £x
Product 16	Ingredient 16	Brand C	Save £x.00
Product 17	Ingredient 17	Brand C	Save £x.00					Save £x
Product 18	Ingredient 18	Brand C						Save £x
Product 19	Ingredient 19	Brand D	Save £x.00
Product 20	Ingredient 20	Brand D	Save £x.00					Save £x						Save £x
Product 21	Ingredient 21	Brand D	Save £x.00
Product 22	Ingredient 22	Brand D	Save £x.00
Product 23	Ingredient 23	Brand D									Save £x
Product 24	Ingredient 24	Brand D												Save £x
Product 25	Ingredient 25	Brand D												Save £x
Product 26	Ingredient 26	Brand D			Save £x						Save £x
Product 27	Ingredient 27	Brand D	Save £x
Product 28	Ingredient 28	Brand D			Save £x
Product 29	Ingredient 29	Brand D			Save £x
Product 30	Ingredient 30	Brand D			Save £x
Product 31	Ingredient 31	Brand D						Save £x
Product 32	Ingredient 32	Brand D												Save £x
Product 33	Ingredient 33	Brand D			Save £x						Save £x
			Aug-2020				Sep-2020				Oct-2020
Category 1	Category 4	Brand	w/c 10-Aug	w/c 17-Aug	w/c 24-Aug	w/c 31-Aug	w/c 07-Sep	w/c 14-Sep	w/c 21-Sep	w/c 28-Sep	w/c 05-Oct	w/c 12-Oct	w/c 19-Oct	w/c 26-Oct
Product 1	Ingredient 1	Brand A	Save £1.00
Product 2	Ingredient 2	Brand A							Save £x.00
Product 3	Ingredient 3	Brand A							Save £x.00
Product 4	Ingredient 4	Brand B										Save £x.00
Product 5	Ingredient 5	Brand B										Save £x.00
Product 6	Ingredient 6	Brand B										Save £x.00
Product 7	Ingredient 7	Brand B										Save £x.00
Product 8	Ingredient 8	Brand B										Save £x.00
Product 9	Ingredient 9	Brand B										Save £x.00
Product 10	Ingredient 10	Brand C										Save £x.00
Product 11	Ingredient 11	Brand C	Save £0.50
Product 12	Ingredient 12	Brand C	Save 25%
Product 13	Ingredient 13	Brand C				Save £0.20						Save £x.00
Product 14	Ingredient 14	Brand C										Save £x.00
Product 15	Ingredient 15	Brand C
Product 16	Ingredient 16	Brand C
Product 17	Ingredient 17	Brand C							Save £x.00
Product 18	Ingredient 18	Brand C
Product 19	Ingredient 19	Brand D										Save £x.00
Product 20	Ingredient 20	Brand D										Save £x.00
Product 21	Ingredient 21	Brand D										Save £x.00
Product 22	Ingredient 22	Brand D										Save £x.00
Product 23	Ingredient 23	Brand D
Product 24	Ingredient 24	Brand D
Product 25	Ingredient 25	Brand D
Product 26	Ingredient 26	Brand D
Product 27	Ingredient 27	Brand D
Product 28	Ingredient 28	Brand D
Product 29	Ingredient 29	Brand D
Product 30	Ingredient 30	Brand D
Product 31	Ingredient 31	Brand D
Product 32	Ingredient 32	Brand D
Product 33	Ingredient 33	Brand D

The same brand might not have the saving applied to this year, which is fine. That's exactly why I'm doing this report

At the moment I am manually cutting the data and pasting it underneath and cleaning the data. I'm also doing this across several files and having to do this every 4 weeks. I'm hoping Power Query can do the heavy lifting for me.