Transform Column Header only MM/DD information for several years

[PowerQueryUser]

I am facing a power query challenge.
I have a csv file with many line items.

The first columns are some material descriptions.
The following columns are weekly dates MM/DD, but without year information.
The csv file covers around 3 years.
Following an example of the structure. Please consider each MM/DD represents one column header.
Just for explanation I added the year and separated in different lines.

2021:
8/1 8/8 8/15 8/22 8/29 9/1 9/5 9/12 9/19 9/26 10/1 10/3 10/10 10/17 10/24 10/31 11/1 11/7 11/14 11/21 11/28 12/1 12/5 12/12 12/19 12/26

2022:
1/1 1/2 1/9 1/16 1/23 1/30 2/1 2/6 2/13 2/20 2/27 3/1 3/6 3/13 3/20 3/27 4/1 4/3 4/10 4/17 4/24 5/1 5/8 5/15 5/22 5/29 6/1 6/5 6/12 6/19 6/26 7/1 7/3 7/10 7/17 7/24 7/31 8/1 8/7 8/14 8/21 8/28 9/1 9/4 9/11 9/18 9/25 10/1 10/2 10/9 10/16 10/23 10/30 11/1 11/6 11/13 11/20 11/27 12/1 12/4 12/11 12/18 12/25

2023:
1/1 1/8 1/15 1/22 1/29 2/1 2/5 2/12 2/19 2/26 3/1 3/5 3/12 3/19 3/26 4/1 4/2 4/9 4/16 4/23 4/30 5/1 5/7 5/14 5/21 5/28 6/1 6/4 6/11 6/18 6/25 7/1 7/2 7/9 7/16 7/23 7/30 8/1 8/6 8/13 8/20 8/27

In the consecutive power query I unpivot the dates.
Then I format each date as data type date.
The issue is, that all dates are formatted with the current year, e.g. 1st August 2021.
I could solve the issue for two years with a conditional column and comparing if the line item date is in the past. Then I knew, that the line time must be in 2022.
But now facing also 2023, this approach does not work anymore.

Do you have any idea, about a logic to format the columns with correct years?

You might recognize, that the dates are basically a weekly pattern. But each month starts also with an additional column. e.g. 29th Aug, 1st Sept, 5th Sept.

I really appreciate a hint, what kind of logic I could apply to solve this challenge.
Thank you very much.

 

[GraH]

By any chance do you have the year in the file names?
It must be somewhere in the data or else the logic may become dodgy.
Owe, wait, only 1 file...

Again not sustainable in the longer run, but you could try this:
- make a calendar table for these 3 years
- keep only Mondays
- add the year column
- add a column to match mm/dd
- merge both table
- extract the year

Would need to find a logic where the in the week you have a new year. Maybe your column with new month might help.

 

[JGordon11]

If your out years will always include a 1/1 column and the total years is always 3 this may work.

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Date.Month([Date]) = 1 and Date.Day([Date])=1 then [Index] else -1),
    lstJanOneRows = List.MaxN( #"Added Conditional Column"[Custom],2),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each 
            if [Index] <lstJanOneRows{1} then [Date]
            else if [Index] <lstJanOneRows{0} then Date.AddYears([Date], 1) else Date.AddYears([Date], 2)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}})
in
    #"Renamed Columns"

pq DateConversion.xlsm
	A	B	C	D	E
4	Desc	Date		Desc	Date
5	Desc1	8/1/2021		Desc1	8/1/2021
6	Desc2	8/8/2021		Desc2	8/8/2021
7	Desc3	8/15/2021		Desc3	8/15/2021
8	Desc4	8/22/2021		Desc4	8/22/2021
9	Desc5	8/29/2021		Desc5	8/29/2021
10	Desc6	9/1/2021		Desc6	9/1/2021
11	Desc7	9/5/2021		Desc7	9/5/2021
12	Desc8	9/12/2021		Desc8	9/12/2021
13	Desc9	9/19/2021		Desc9	9/19/2021
14	Desc10	9/26/2021		Desc10	9/26/2021
15	Desc11	10/1/2021		Desc11	10/1/2021
16	Desc12	10/3/2021		Desc12	10/3/2021
17	Desc13	10/10/2021		Desc13	10/10/2021
18	Desc14	10/17/2021		Desc14	10/17/2021
19	Desc15	10/24/2021		Desc15	10/24/2021
20	Desc16	10/31/2021		Desc16	10/31/2021
21	Desc17	11/1/2021		Desc17	11/1/2021
22	Desc18	11/7/2021		Desc18	11/7/2021
23	Desc19	11/14/2021		Desc19	11/14/2021
24	Desc20	11/21/2021		Desc20	11/21/2021
25	Desc21	11/28/2021		Desc21	11/28/2021
26	Desc22	12/1/2021		Desc22	12/1/2021
27	Desc23	12/5/2021		Desc23	12/5/2021
28	Desc24	12/12/2021		Desc24	12/12/2021
29	Desc25	12/19/2021		Desc25	12/19/2021
30	Desc26	12/26/2021		Desc26	12/26/2021
31	Desc27	1/1/2021		Desc27	1/1/2022
32	Desc28	1/2/2021		Desc28	1/2/2022
33	Desc29	1/9/2021		Desc29	1/9/2022
34	Desc30	1/16/2021		Desc30	1/16/2022
35	Desc31	1/23/2021		Desc31	1/23/2022
36	Desc32	1/30/2021		Desc32	1/30/2022
37	Desc33	2/1/2021		Desc33	2/1/2022
38	Desc34	2/6/2021		Desc34	2/6/2022
39	Desc35	2/13/2021		Desc35	2/13/2022
40	Desc36	2/20/2021		Desc36	2/20/2022
41	Desc37	2/27/2021		Desc37	2/27/2022
42	Desc38	3/1/2021		Desc38	3/1/2022
43	Desc39	3/6/2021		Desc39	3/6/2022
44	Desc40	3/13/2021		Desc40	3/13/2022
45	Desc41	3/20/2021		Desc41	3/20/2022
46	Desc42	3/27/2021		Desc42	3/27/2022
47	Desc43	4/1/2021		Desc43	4/1/2022
48	Desc44	4/3/2021		Desc44	4/3/2022
49	Desc45	4/10/2021		Desc45	4/10/2022
50	Desc46	4/17/2021		Desc46	4/17/2022
51	Desc47	4/24/2021		Desc47	4/24/2022
52	Desc48	5/1/2021		Desc48	5/1/2022
53	Desc49	5/8/2021		Desc49	5/8/2022
54	Desc50	5/15/2021		Desc50	5/15/2022
55	Desc51	5/22/2021		Desc51	5/22/2022
56	Desc52	5/29/2021		Desc52	5/29/2022
57	Desc53	6/1/2021		Desc53	6/1/2022
58	Desc54	6/5/2021		Desc54	6/5/2022
59	Desc55	6/12/2021		Desc55	6/12/2022
60	Desc56	6/19/2021		Desc56	6/19/2022
61	Desc57	6/26/2021		Desc57	6/26/2022
62	Desc58	7/1/2021		Desc58	7/1/2022
63	Desc59	7/3/2021		Desc59	7/3/2022
64	Desc60	7/10/2021		Desc60	7/10/2022
65	Desc61	7/17/2021		Desc61	7/17/2022
66	Desc62	7/24/2021		Desc62	7/24/2022
67	Desc63	7/31/2021		Desc63	7/31/2022
68	Desc64	8/1/2021		Desc64	8/1/2022
69	Desc65	8/7/2021		Desc65	8/7/2022
70	Desc66	8/14/2021		Desc66	8/14/2022
71	Desc67	8/21/2021		Desc67	8/21/2022
72	Desc68	8/28/2021		Desc68	8/28/2022
73	Desc69	9/1/2021		Desc69	9/1/2022
74	Desc70	9/4/2021		Desc70	9/4/2022
75	Desc71	9/11/2021		Desc71	9/11/2022
76	Desc72	9/18/2021		Desc72	9/18/2022
77	Desc73	9/25/2021		Desc73	9/25/2022
78	Desc74	10/1/2021		Desc74	10/1/2022
79	Desc75	10/2/2021		Desc75	10/2/2022
80	Desc76	10/9/2021		Desc76	10/9/2022
81	Desc77	10/16/2021		Desc77	10/16/2022
82	Desc78	10/23/2021		Desc78	10/23/2022
83	Desc79	10/30/2021		Desc79	10/30/2022
84	Desc80	11/1/2021		Desc80	11/1/2022
85	Desc81	11/6/2021		Desc81	11/6/2022
86	Desc82	11/13/2021		Desc82	11/13/2022
87	Desc83	11/20/2021		Desc83	11/20/2022
88	Desc84	11/27/2021		Desc84	11/27/2022
89	Desc85	12/1/2021		Desc85	12/1/2022
90	Desc86	12/4/2021		Desc86	12/4/2022
91	Desc87	12/11/2021		Desc87	12/11/2022
92	Desc88	12/18/2021		Desc88	12/18/2022
93	Desc89	12/25/2021		Desc89	12/25/2022
94	Desc90	1/1/2021		Desc90	1/1/2023
95	Desc91	1/8/2021		Desc91	1/8/2023
96	Desc92	1/15/2021		Desc92	1/15/2023
97	Desc93	1/22/2021		Desc93	1/22/2023
98	Desc94	1/29/2021		Desc94	1/29/2023
99	Desc95	2/1/2021		Desc95	2/1/2023
100	Desc96	2/5/2021		Desc96	2/5/2023
101	Desc97	2/12/2021		Desc97	2/12/2023
102	Desc98	2/19/2021		Desc98	2/19/2023
103	Desc99	2/26/2021		Desc99	2/26/2023
104	Desc100	3/1/2021		Desc100	3/1/2023
105	Desc101	3/5/2021		Desc101	3/5/2023
106	Desc102	3/12/2021		Desc102	3/12/2023
107	Desc103	3/19/2021		Desc103	3/19/2023
108	Desc104	3/26/2021		Desc104	3/26/2023
109	Desc105	4/1/2021		Desc105	4/1/2023
110	Desc106	4/2/2021		Desc106	4/2/2023
111	Desc107	4/9/2021		Desc107	4/9/2023
112	Desc108	4/16/2021		Desc108	4/16/2023
113	Desc109	4/23/2021		Desc109	4/23/2023
114	Desc110	4/30/2021		Desc110	4/30/2023
115	Desc111	5/1/2021		Desc111	5/1/2023
116	Desc112	5/7/2021		Desc112	5/7/2023
117	Desc113	5/14/2021		Desc113	5/14/2023
118	Desc114	5/21/2021		Desc114	5/21/2023
119	Desc115	5/28/2021		Desc115	5/28/2023
120	Desc116	6/1/2021		Desc116	6/1/2023
121	Desc117	6/4/2021		Desc117	6/4/2023
122	Desc118	6/11/2021		Desc118	6/11/2023
123	Desc119	6/18/2021		Desc119	6/18/2023
124	Desc120	6/25/2021		Desc120	6/25/2023
125	Desc121	7/1/2021		Desc121	7/1/2023
126	Desc122	7/2/2021		Desc122	7/2/2023
127	Desc123	7/9/2021		Desc123	7/9/2023
128	Desc124	7/16/2021		Desc124	7/16/2023
129	Desc125	7/23/2021		Desc125	7/23/2023
130	Desc126	7/30/2021		Desc126	7/30/2023
131	Desc127	8/1/2021		Desc127	8/1/2023
132	Desc128	8/6/2021		Desc128	8/6/2023
133	Desc129	8/13/2021		Desc129	8/13/2023
134	Desc130	8/20/2021		Desc130	8/20/2023
135	Desc131	8/27/2021		Desc131	8/27/2023
Sheet2

 

[PowerQueryUser]

@GraH, @JGordon11 thank you so much for your quick response and great help.
@JGordon11, I could rebuild your code in a simplified testing file. It is working great. Next week I will try to implement it into my data file. I will come back with some feedback.

Really great help. Especially as I am not yet experienced with power query.