Hello Experts, How can I transform below table to desired output. input: movie Name10 Cloverfield Lane13th20th Century Women19451985Number of Critical Reviews301952176743Rating 90% 97% 89% 97% 95%Year20162016201720172018 Output: Movie NameNumber of Critical ReviewsRatingYear10 Cloverfield Lane301 90%201613th95 97%201620th Century Women217 89%2017194567 97%2017198543 95%2018

Transpose tool - Transform

Hello Experts,

How can I transform below table to desired output.

input:

movie Name	10 Cloverfield Lane	13th	20th Century Women	1945	1985
Number of Critical Reviews	301	95	217	67	43
Rating	90%	97%	89%	97%	95%
Year	2016	2016	2017	2017	2018

Output:

Movie Name	Number of Critical Reviews	Rating	Year
10 Cloverfield Lane	301	90%	2016
13th	95	97%	2016
20th Century Women	217	89%	2017
1945	67	97%	2017
1985	43	95%	2018

Transformation

Preparation

Accepted answers

grossal

Hi @Ravy12,

here is a quick way to solve your problem.

I know other people will probably use different tools, but for me, that's the quickest solution.

The Python-Tool only needs one line to fully shift the data and the dynamic rename is used to use the first data row as column headers.

Example workflow attached to see all configurations.

If this solved you problem, please consider to accept it as a solution or drop another question if it doesn't.

Alex

2020-03-13-Transform.yxmd

danilang

Hi @grossal

Python definitely takes the win in terms of number of tools. Here's how you can do it without calling in the snake

If you don't care about row order in your output, you're pretty well done after the Dynamic Rename. To get the correct row order, use a Field Info to get the original column names and add a RowOrder. Join this to output, sort and clean up

Dan

Transpose.yxmd

grossal

Hi @danilang,

it's kinda sad that there isn't a "Shift full data" tool by default tbh.

I have tried to motivate myself to not use Python when using Alteryx, but I can't withstand when I come around this problem. I was running into the exact same issue multiple times and even create a macro for it to make it a "one tool" thing.