In this blog I will show you how to transform a table of columns into rows using data from an excel file (you can also use other file formats that are supported by Alteryx).

Alteryx Workflow

 

Step 1: Input the data (I’m using GDP from 1970 to 1979 in a excel file) into Alteryx using the Input tool. Let’s have a look at the output anchor of the Input tool in the Results window to check what data cleaning is required.

Output in the results window for Input Tool

One of the columns F12 has only nulls, therefore it can be removed to optimize the workflow.  I would like to remove the * after the country name in column F1 and get rid of the (or area) after Country in the first row of the F1 column.

 

Step 2: Use the select tool to remove the F12 column that contains only nulls and check the data type of all the fields.

 

Step 3: Using filter function remove any the special character * that we have after each country and the extra writing of (or area) after the Country.

Configuration window of the Filter Tool

 

Step 4: Using the Dynamic Rename tool change the first row of data into the header for each column.

Output in the results window for the Dynamic Rename Tool

 

Step 5: Transpose the columns with Year headers into rows using the transpose tool.

Results window of Transpose Tool

 

Step 6: Correctly rename the Name and Value columns as Year and GDP respectively using the Select tool.

Results window for the Output Tool

 

Step 7: Output the results into the desired file format using Output tool.

I wanted the GDP data in this format to add to another data set which had Year as a column.

 

 

Anusha Kola
Author: Anusha Kola