Transpose Tool Problem/Example

[mp2624]

Hi All,

I have a bit of a problem figuring out how to clean/transpose my source table which currently looks like this:

Entity Name	Expense A	Expense A	Expense A	Expense B	Expense B	Expense B
	2017	2016	2015	2017	2016	2015
A	1	3	3	8	4	11
B	2	3	45	7	7	12
C	2	6	6	6	9	13

Ideally the table I want should look something like this: 

Entity Name	Year	Expense	Value
A	2017	Expense A	XX
A	2016	Expense A	XX
A	2015	Expense A	XX
A	2017	Expense B	XX
A	2016	Expense B	XX
A	2015	Expense B	XX
B	2017	Expense A	XX
B	2016	Expense A	XX
B	2015	Expense A	XX
B	2017	Expense B	XX
B	2016	Expense B	XX
B	2015	Expense B	XX
C	2017	Expense A	XX
C	2016	Expense A	XX
C	2015	Expense A	XX
C	2017	Expense B	XX
C	2016	Expense B	XX
C	2015	Expense B	XX

Any help with the workflow is much appreciated!!!

[jasperlch]

Hi @mp2624, 

I would suggest taking the first 2 header rows out and concat them by the Summarize tool before joining this new header row back to the dataset. Then, just do the Transpose as normal and separate Year and Expense by Text to Column.

 

Example attached. Hope this helps.

[Thableaus · Accepted solution]

Hi @mp2624!

Here's the solution that I thought. Workflow appended (fewer steps).

Cheers,

[ponraj]

Here is the sample workflow for your case. Hope this is helpful. 

workflowResults

[mp2624]

Thank you very much all, problem solved!