Unpivot columns with multiple relevant rows with headers. PowerQuery M language | Multiple Unpivot simultaneously

Question

I have a table which have multiple headers in the rows that I want to keep when I unpivot my table, but I couldn't find a way to unpivot it as needed. Please find below the example of the original table

		Actuals	Actuals	Plan	Plan
		FY20	FY20	FY21	FY21
Country	Owner	1/1/2020	1/1/2020	1/1/2020	1/1/2020
France	Richard	100	150	80	160
France	Martin	120	140	130	140
France	Pierre	50	100	50	80

Below is what I want to achieve (unpivot the 4 columns, but keep the headers in the rows as new columns)

Country	Owner	Version	Year	Date	Value
France	Richard	Actuals	FY20	1/1/2020	100
France	Richard	Actuals	FY21	1/1/2020	150
France	Richard	Plan	FY20	1/1/2020	80
France	Richard	Plan	FY21	1/1/2020	160
France	Martin	Actuals	FY20	1/1/2020	120
France	Martin	Actuals	FY21	1/1/2020	140
France	Martin	Plan	FY20	1/1/2020	130
France	Martin	Plan	FY21	1/1/2020	140
France	Pierre	Actuals	FY20	1/1/2020	50
France	Pierre	Actuals	FY21	1/1/2020	100
France	Pierre	Plan	FY20	1/1/2020	50
France	Pierre	Plan	FY21	1/1/2020	80

I believe this can only be achieved with the custom code, but I'm not familiar with it apart of the standard functionalities.

Thank you in advance and please let me know if any other info is relevant.

[Edit: The date in the table for Actuals should be FY20 and FY21, and the year should change as well, same for the Plan, should be FY20 and FY21 and the year should change as well. I won't change now as that would change the answers that solved my issue of converting the multiplied headers in the rows to columns]

Cheers

Answer 1

let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
NewNames=List.Transform(List.Zip({
        Record.FieldValues(Source{0}),
        Record.FieldValues(Source{1}),
        List.Transform(Record.FieldValues(Source{2}), each Text.From(_)
        )}), each Text.Combine(_,":")),
Rename=Table.RenameColumns(Table.Skip(Source,3),List.Zip({Table.ColumnNames(Source),NewNames})),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Rename, {"Country", "Owner"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Version", "Year", "Date"}),
Touchup=Table.TransformColumns(#"Split Column by Delimiter",{{"Version", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}}),
Touchup2=Table.TransformColumns(Touchup,{{"Date", each Date.From(Number.From(_))}}),
#"Changed Type" = Table.TransformColumnTypes(Touchup2,{{"Date", type date}})
in #"Changed Type"