How to Pivot and Transpose Data in Alteryx

Alteryx provides two tools that help you rotate, pivot, and transpose data. The Cross Tab tool allows users to convert from rows to columns, and the Transpose tool allows you to convert from columns to rows. Both tools are available in the Transform section of the Alteryx Toolbar.

The Transpose and Cross Tab tools are used in alteryx to convert columns to rows or rows to columns.

When to use the Transpose and Cross Tab Tools in Alteryx are two of the most confusing concepts in data transformation. Our guess is that the average human brain wasn’t designed to thinking about data tables rotating around and pivoting naturally. Luckily, Alteryx gives you several visual ques and examples built into Designer to help you remember which tools to use when.

Let’s dive in!

Transpose, Pivot, Unpivot, and Cross Tab Confusion

One problem that adds to the confusion around which tool to use when is that people working in Alteryx often work across multiple other software platforms throughout the day. The naming conventions between solutions are different. For example, Power Query, a data transformation solution available in Excel and Power BI calls rotating data Pivot and UnPivot. Regular Excel uses the term Transpose and Pivot Tables could go either way depending on configuration.

We point this out because it can be difficult enough to remember which tool does what, but instead of terminology we’ll focus on the scenarios of transforming columns to rows or rows to columns

Converting Rows to Columns with Cross Tab

Use the Alteryx Cross Tab Tool from the Transpose section of the Alteryx Toolbar to convert from rows to columns. A common use case is having a single column of dates that you want to go across the top of your data from left to right.

The example below shows the before and after of a configured cross tab tool. The Sales Date column is in a table with many rows of dates which are transformed to become the new column headers.

Example of a Cross Tab tool converting from multiple rows in a single column to multiple column headers

The Cross Tab tool is configured using several different sections.

  • Group Data by These Values – These are the columns you want to keep on the left side of your workflow results. They become groups that work similar to a Summarize Group by Tool. They are the aggregation levels used when multiple of the same combination exist in a data table.
  • Change Column Headers – This is single Column that becomes Multiple Columns.
  • Values for New Columns – Values fill in the new grid. This is the data being presented after the transformation.
  • Method for Aggregating Values – How Alteryx should handle if multiple rows exist for grouping. For example if a category contains multiple rows it will aggregate them. Which method of aggregation should Alteryx use?

Here’s what the configuration of the example above would look like:

Configuration panel of the Cross Tab tool in Alteryx

The Group Data by These Values section is optional, you can group by a single, multiple columns, or no columns at all.

Converting Columns to Rows with Transpose

Transposing data in Alteryx is done using the Transpose Tool in the Transform section of the Alteryx Toolbar. The Transpose tool transforms multiple columns to a single column with many values. It is the inverse of the Cross Tab Tool. Configure the tool by selecting the Columns to Keep, and a new Name and Value column will be generated.

The example below shows many columns being transposed into a single column with many rows.

The values from the starting table, on the left are also transformed from a matrix to a single column. Values that are transposed result in two new columns. The Name column is the name of the previous column headers. Values are the previous values that made up the grid or matrix of values in a table.

When no combination of values are present for a specific cell, Alteryx will note it as Null another way of saying that it has no value.

Example of transposing multiple columns of dates to a single column of dates with multiple rows

The Transpose tool is configured by selecting Key Columns, or the columns you want to keep on the left side of your workflow results. These are often categories that the previous table was grouped by.

The remaining column names will be left down below. These are the columns that will be transformed into a single column of values.

How to configure an Alteryx Transpose Tool

While Cross Tab converts rows to columns, Transpose converts columns to rows.

Cross Tab vs Transpose

The easiest way to remember which Alteryx Tool converts columns to rows and rows to columns is to take note of the icon used for each tool. Follow the arrow on the icon to see a Single Column of Rows going to Multiple Columns (Cross Tab) or Multiple Columns going to a Single Column of Rows (Transpose)

Comparison of Transpose and Cross Tab

Another option is to right-click on each one of the tools from the Alteryx Toolbar and select “Open Example”. Alteryx provides pre-made mini-workflows that show you how to configure most of the tools available along with example scenarios so you can see the before, and after with different tool configurations.

How to view tool examples in Alteryx

The examples for Transpose and Cross Tab also contain diagrams of what each one does.

An example of the transpose tool in Alteryx with multiple configurations and a diagram of data transposition

There are also times when we are not sure how the data we are using needs to be transformed which leads us to “Guess and Check” by adding both tools to the workflow and connecting it to our data stream. It typically becomes pretty apparent when you go to configure the tool that there is a lack of options or the results of one will look completely off after running the workflow.

Using the Alteryx Transpose and Cross Tab Tools Together

It’s important to know that Transpose and Cross Tab are not mutually exclusive in Alteryx, meaning that there are times when it makes sense to use both tools together. For example, you can Transpose data to put all of your column headers into a single column where it can have filters applied to it. Once the list of columns is filtered, use a Cross Tab tool to place the remaining filtered columns back to column headers.

This is a common method to filter the columns available in your workflow’s results dynamically. Other methods involve using a Select Tool to individually select or de-select columns which is not practical when column headers change over time or if you are working with hundreds of columns.

Example of using a workflow with a transpose and cross tab tool together to dynamically filter column names

Dynamic Rename to Replace Values in Column Names

One annoyance with using the Cross Tab tool in Alteryx is that it does not support special characters or spaces. The tool will automatically replace any special character with an under-score. You can replace the underscore by adding a Dynamic Rename tool.

The Dynamic Rename tool lets you use Formulas to Replace characters. Use the syntax: Replace([_CurrentField_],”_”,”-“) to replace an under-score with a hyphen for example. The [_CurrentField_] field name is a place holder that applies the same formula to all of the columns selected at the top of the Dynamic Rename Tool.

Using a dynamic rename tool to replace under-scores that replaced special characters and spaces during a cross tab

This is also the same tool that you can use to take field names from the top row of data or from an entirely separate data set. It’s available in the Developer section of the Alteryx Toolbar.

Conclusion

Transposing and pivoting data in Alteryx can be performed with the Transpose and Cross Tab tools. They let you quickly switch from multiple columns to a single column (Transpose) or a single column to multiple columns (Cross Tab).

Each tool can take a bit of getting used to, but the icon of each tool gives a hint of which one to use for your specific use case, or you can right click on each tool and Open an Example to view pre-made example workflows and diagrams.

It’s not uncommon to use both tools together to transpose data, filter out specific columns or values, and then apply a cross tab to move the data back into it’s original position.

Scroll to Top