Concatenate rows and create headers

[analysiswithtech]

	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

[fmvizcaino · Accepted solution]

Hi @analysiswithtech ,

Take a look at the attached example. I'm using group by to concatenate the rows based in recordID number and transposing as you need. The only thing left to do is to order your data properly.

Let me know if that works for you.

Best,

Fernando Vizcaino

[AndyMoncla]

Nice use of the Summarize and Dynamic Rename tools.  Good work!