Concatenate rows and create headers

[analysiswithtec]

	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019	2019
	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Actual	Forecast
Sales	100	100	100	100	100	100	100	100	100	100	100	100
Cost	-50	-50	-50	-50	-50	-50	-50	-50	-50	-50	-50	-50

I have a data set that looks like the above.

I want to create header names that concatenate the three first rows and then unpivot the data, to get something like this:

2019-Jan-Actual	Sales	100
2019-Jan-Actual	Cost	-50
2019-Feb-Actual	Sales	100
2019-Feb-Actual	Cost	-50
2019-Mar-Actual	Sales	100
2019-Mar-Actual	Cost	-50

If you could help that would be great. Thanks

[Mariusz · Accepted solution]

Hi @analysiswithtec 

Please see the attached file with all the steps included.

Personally I would skip the last two steps "Unpivoted Columns"  and "Merged Columns" as its easier to work with this format but I'm sure that you have valid reasons.

Best Regards,
Mariusz
 
If this post helps, then please consider Accepting it as the solution.