Alteryx tip: Using Transpose and Cross Tab to swap column values and headers

by George Pattinson

This is a very quick blog post to hopefully save someone some time when trying to move values from a column to column headers.

In this example, a simple set of data has been put together using the text input tool:

Input data table

The aim is to take the values in Field 1 and move them to the column headings whilst maintaining all data. This should be quite an easy task, but has taken me a disproportionately long time to figure out in Alteryx - and the solution lies in using the Transpose and Cross Tab tools.

The end result is shown below:

Desired result, and workflow above

Step 1: Use the Transpose tool to pivot Field2-5 column headings into the table, whilst selecting the  Field1 as the key column. The configuration is as follows:

Transpose tool configuration

Step 2: Using the Cross Tab tool, move Field1 into the column headers, grouping on Name. The configuration for the Cross Tab tool is as follows:

By grouping on Name in this final step, which is a seemingly irrelevant field, the data is not aggregated into the same cell and the desired result is reached.