Changing 4 rows of data to 4 columns, using the first column as headers

[MATTHEW_KING1]

Was hoping someone could please help me understand how to change the below 4 rows of data into 4 columns of data with Name, Number of Critical Reviews, Rating and Year as column headers? Thanks in advance.

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

[atcodedog05]

Hi @MATTHEW_KING1 

Hope this is what you are looking for. I am using transpose and crosstab to achieve it.

1. Using record id tool to set key column.

2. Keeping record id as key transpose all columns.

3. Using field name as key, name as record id and value as value crosstabing it to table

4. Using dynamic rename to take names from first row.

5. Data cleanse to clean spaces

Hope this helps 🙂

[MATTHEW_KING1]

Thanks, much appreciated. I thought it would be simpler than this to be honest, a simple transpose in Excel but a fair few more steps in Alteryx. Will take some getting used to.

[MATTHEW_KING1]

Out of interest, what would be the solution using a Input Data tool instead of the Text Input tool?

[atcodedog05]

Hi @MATTHEW_KING1 

It will be same just replace it. You might have to set first row contains data option for your current scenario

Hope this helps 🙂

[atcodedog05 · Accepted solution]

Hi @MATTHEW_KING1 

Here is a workflow using excel file. I am using the same steps suggested above.

Input tool:

Workflow:

Hope this helps 🙂

[mceleavey]

HI @MATTHEW_KING1 ,

yeah, it is just the same in Alteryx but the beauty of Alteryx is you only do it once. No more manual messing around in Excel.

Friends don't let friends Excel.

M.

[MATTHEW_KING1]

Perfect, thanks so much.