Creating a long list of data

[anujkasera]

I need to arrange my data in a table which has only 3 columns - Site ID, attribute aggregation (Time + Metric + Type), Metric value. I tried using the transpose but that is not working. Can someone please suggest the best way to do this? Thanks!

Current

Site	Period	Revenues	Units	Customer Type
1	TY	100	10	A
1	TY	95	11	B
1	LY	105	9	C

Desired

Site	Agg	Metric
1	TY Rev A	100
1	TY Units A	10
1	TY Rev B	95
2
2

[jdunkerley79 · Accepted solution]

First use a Transpose table to convert to Site, Period, Customer Type, Name, Value

You can then use a Formula tool to make the Agg field

Finally a select to rename and drop columns

[DultonM · Accepted solution]

Hi anujkasera!

You're almost there! To add to jdunkerley79's post:

Choose Site, Period, and Customer Type as your "Key Fields" in the Transpose tool. I've attached a workflow (built in 10.5) that demonstrates this and the Formula/Select tools that should follow.

UPDATE: Oops! Didn't realize jdunkerley79's post also included a workflow! Looks like you get extra assurance that this'll work 🙂

[anujkasera]

That worked! Thank you for the help jdunkerley79 and DultonM. Very helpful!