Alteryx offers a powerful set of tools and features with which you can transform data - however, you might find that there's no premade tool for switching/rotating rows and columns.
Users coming from Microsoft software might notice the lack of this feature offered by Excel and PowerQuery's 'Transpose' function.
In Alteryx, this is done with a combination of the Transpose and Cross-Tab functions (Alteryx's versions of 'Unpivot' and 'Pivot', respectively).
We'll also use the Record ID tool to add a unique number for each row (if your table doesn't have one already).
Step 1: Record ID and Transpose
First, apply a Record ID, found under the 'Preparation' tab.
- Set [Record ID] in 'Key Columns', and everything else as in 'Data Columns'.
This places your whole table into a tall format:
The data used to be in one row and multiple columns, but now is now in multiple rows and fewer columns.
To learn more about tall and wide data formats, read here.
Step 2: Cross-Tab
- Group by [Name]
(These would be your original headers) - Set [Record ID] to 'Change Column Headers
- Set [Values] as 'Values for New Columns'
- Aggregate by 'First'
Your table should be converted from tall format back to wide format.
You'll notice that the rows and columns of the entire table have been switched from what we originally had - just with the addition of RowID, which are now the headers for each column.
So.. what's happening here?
- Grouping by [Name] (your original headers) creates one row for each column your table originally had.
- Changing column headers to [Record ID] forces Cross-tab to create one column for each row your table originally had.
Step 3 (Optional): Choose your Column headers
What if you don't want a Row ID Number as your column headers?
For the example below, we want the column headers to be:
'Year', '2012', '2013', '2014'
instead of 'Name' and Row ID numbers.
You can use the Dynamic Rename tool to turn a row into a set of column headers.
Just set the 'Rename Mode' to 'Take Field Names from First Row of Data', and you're done!
If, however, the row you want to turn into column headers isn't the first row, use a quick Formula tool + Sort tool to set the row you want to be the first row.
Use a formula to create a new row, and set it to 0 if it's the row we want, and 1 if it's any other row.
Use the sort tool to reorder the data by the formula you just made, so the row you marked becomes the first row.
Once this is done, use the Dynamic Rename tool to turn the first row into column headers.
For the earlier example. it'll now look like this:
Use the Select tool to remove the output of the formula tool (the '0' column), and you're done!
In summary, use the Row ID, Transpose and Cross-Tab tools to switch your rows and columns.
To make your own column headers, use the Dynamic Rename tool.