How to split one row into multiple rows based on specific criteria

[Mcansi]

Hello - I'm attempting to split one row into multiple based on the number of years. Example below:

This is the source data. The dates are for every month in 2022, 2023, and 2024 but I've condensed it keep it simple.

ID	Account	1/31/2022	2/28/2022	1/31/2023	2/28/2023	1/31/2024	2/28/2024
1234	1111	100	100	200	200	300	300
1235	2222	95	95	105	105	115	115

Below is what I hope to achieve:

ID	Account	Year	Jan	Feb
1234	1111	2022	100	100
1234	1111	2023	200	200
1234	1111	2024	300	300
1235	2222	2022	95	95
1235	2222	2023	105	105
1235	2222	2024	115	115

I'd prefer to use index match formula somehow but am having a hard time getting it to work. Any help would be much appreciated

 

[alansidman]

With Power Query, unpivot your data so that you can repivot into desired format

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Account	1/31/2022	2/28/2022	1/31/2023	2/28/2023	1/31/2024	2/28/2024
2	1234	1111	100	100	200	200	300	300		<--Orig Data
3	1235	2222	95	95	105	105	115	115
4
5
6	ID	Account	Value	Year	Month Name		Sum of Value			Month Name
7	1234	1111	100	2022	January		ID	Account	Year	January	February
8	1234	1111	100	2022	February		1234	1111	2022	100	100
9	1234	1111	200	2023	January				2023	200	200
10	1234	1111	200	2023	February				2024	300	300
11	1234	1111	300	2024	January		1235	2222	2022	95	95
12	1234	1111	300	2024	February				2023	105	105
13	1235	2222	95	2022	January				2024	115	115
14	1235	2222	95	2022	February
15	1235	2222	105	2023	January
16	1235	2222	105	2023	February
17	1235	2222	115	2024	January
18	1235	2222	115	2024	February
Sheet1

Mcode to unpivot

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Account"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Attribute]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Attribute]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month Name",{"Attribute"})
in
    #"Removed Columns"

Pivot Table set up