Transpose and Crosstab

Data is not always delivered in the best format. Knowing how data is structured and which arrangement we need is essential to effective analysis. In Alteryx, data can be transformed using several tools including Transpose and Crosstab.

Transpose is used to unpivot fields from a wide dataset, transforming it into one that is more long and narrow (see example below). It is key to configure transpose correctly. First, key columns should be specified (these will be unaffected by the crosstab tool) and data columns (these will be transposed). Transposed fields are always arranged into two columns: 'name' containing previous field headers and 'value' containing the values that were found within these fields.

Conversely, the crosstab tool is used to pivot fields from a long, narrow dataset, producing one that is wider. A new column is created for each unique value within the pivoted field (see example below). In terms of configuration, Alteryx requires us to specify how we wish to group the output. These fields will be unaffected by the crosstab tool. Under the change columns headers field, we can specify which should become headers for the new columns. Values for these columns must also be selected. Finally, we can select which method for aggregate values. In other words, rows with identical values in each of the grouping fields are aggregated. We must therefore specify the type e.g. sum, average, maximum.

Author:
Tom Dobson
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab