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).
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.
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.
Step 4: Using the Dynamic Rename tool change the first row of data into the header for each column.
Step 5: Transpose the columns with Year headers into rows using the transpose tool.
Step 6: Correctly rename the Name and Value columns as Year and GDP respectively using the Select 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.