Transpose a Line with date

[Hakimipous]

Hello!

I'd like to ask the community about a case where a Line should become a column in the following way

Input :

F1	F2	F3	F4	F5	F6
Descriptions	Oct 2018	Oct 2018	Dec 2018	Dec 2018	Dec 2018
Section 1	25	Brussels	100	150	Leuven
1_B	N/A	None	50	A	200
2_B	None
Section 2	50	Anvers	B	C	D

Desired Output

F1 (Period)	F2 Desctiption	Value
Oct 2018	Section 1	25
Oct 2018	Section 1	Brussels
Oct 2018	1_B	N/A
Oct 2018	1_B	None
Oct 2018	2_B	None
Oct 2018	2_B
Oct 2018	Section 2	50
Oct 2018	Section 2	Anvers
Dec 2018	Section 1	100
Dec 2018	Section 1	150
Dec 2018	Section 1	Leuven
Dec 2018	1_B	50
Dec 2018	1_B	A
Dec 2018	1_B	200
Dec 2018	2_B
Dec 2018	2_B
Dec 2018	2_B
Dec 2018	Section 2	B
Dec 2018	Section 2	C
Dec 2018	Section 2	D

I need to transpose my first column and create a "period dimension" for each description

Any help would be appreciated

Thanks !

[BrandonB · Accepted solution]

Solution attached

[BrandonB]

First you need to use a dynamic rename to take the time period names as column headers. As you may notice, due to duplicate time periods, it will rename these headers to have 2, 3, etc afterwards. This isn't an issue as I resolve it further down the line. After that step I used a transpose with the Descriptions column as the key field. This transposes the rest of your data into the proper format. I then used a select tool to reorganize and rename the column headers. Finally, I used a formula tool to trim off the end of the renamed time period fields so that they wouldn't have the _2 or _3 at the end. 

Should be all set after that!

[Hakimipous]

That was shorter than I thought :o

Thanks again @BrandonB  !

[BrandonB]

Happy to help!