Table transformation

[pect]

Hello everybody,

I would like to transform the following table:

Contracte	Tipus contracte	Contractació any 2017	Adjudicat any 2017	Contractació any 2018	Adjudicat any 2018
1	A	1000	2000	4000	2500
2	B	5600	2400	3500	5500

Into the following:

Contracte	Tipus contracte	Estat	Any	Import
1	A	Contractació	2017	1000
1	A	Adjudicat	2017	2000
1	A	Contractació	2018	4000
1	A	Adjudicat	2018	2500
2	B	Contractació	2017	5600
2	B	Adjudicat	2017	2400
2	B	Contractació	2018	3500
2	B	Adjudicat	2018	5500

Could anyone give me a hand please?

Thank you very much in advance!

[MarcelBeug · Accepted solution]

Select the first 2 columns, unpivot other columns.

Adjust the generated code with regard to the names of the new columns.

Split the Estat column with custom delimiter " any ".
Adjust both lines of the generated code with regard to the names of the new columns.

let
    Source = Table3,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Contracte", "Tipus contracte"}, "Estat", "Import"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Estat", Splitter.SplitTextByDelimiter(" any ", QuoteStyle.Csv), {"Estat", "Any"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Estat", type text}, {"Any", Int64.Type}})
in
    #"Changed Type"

[pect]

Thank you very much!

It certainly works!