Tableau Desktop 9.0 (the latest release) delivers two on-the-fly transformations. Split and pivot operations help eliminate a lot of work that we used to take back to our code.
Data is never perfect. Each use case brings its own unique challenges. Two of the most common data manipulations turn single columns of data into many and turn many into one.
Split Transformations
Split transformations enable us to take a single, delimited field from our data and split a single column into several columns automatically. In Tableau Desktop, the procedure is as simple as a right-click.
To do a split, start at the Tableau Desktop introduction page and connect to a new data source as you normally would. When you’re connected, you’ll see the data window, where you can establish joins between your connected tables. This is what you’ll see: Newly split columns and a number of rows.
The Order ID column is delimited by two hyphens to separate the state, year, and order ID number. By clicking on the dropdown menu next to Order ID, you’ll see the new split and pivot options.
By simply selecting the split option, Tableau Desktop recognizes the hyphens in the field and treats them as delimiters. Then, it performs a split transformation, turning the single column into three. Tableau Desktop keeps the original column and provides three new ones identified by the original name and the order of the split you just performed. After the split, you can rename new columns as you see fit.
This is what our example looks like after the split but before the columns are renamed.
Custom split transformations
Tableau Desktop 9.0 also provides a custom split option. For example, you might want to split only the State information from our original column and leave the others intact. The Custom Split window enables you to specify the character you want to use as a delimiter and the specified number of the first or last column.
In our example, simply specify a split of the first column to retrieve a new column, which identifies states in our data.
The split option is not limited to the data window. After you develop a dashboard, you can use the split function on the fly.
To use the Order ID column from within a visualization, right-click the axis. The split function will be available for use.
There is an important difference between performing the split at the data window rather than in a visualization. Splitting the columns at the data window enables you to use those columns as measures or dimensions in your worksheet. When you split columns from worksheets, Tableau Desktop 9.0 provides temporary columns, which you can work from.
You can make the columns permanent, however, by dragging one of the newly split columns to your Dimensions/Measures panel.
Pivot Transformations
Just as you can break a single column using split transformations, you can turn several columns into one by using pivot transformations. You actually turn multiple columns into two—pivot field names and values.
To perform a pivot, start from the Data Source window and select the columns you want to transform. In our example, the Category and Sub-category columns are selected with Ctrl + right-click on one of the columns.
When you right-click a column, the Pivot window appears. Tableau Desktop combines data from the selected columns and displays it in the Pivot field values column. These results appear in a category column called Pivot field names.
If you are comfortable thinking of this as an unpivot function, that’s fine. Just remember that when you do pivot transformations in Tableau Desktop 9.0, you turn columns into rows.
As always, feel free to reach out with any questions or comments by contacting us.
Chris Dugas
Senior Analytics Consultant
About Chris: Chris is a Senior Analytics Consultant and has had experience working in the domestic and international finance sector, distribution and supply chain management, energy, pharmaceutical and sports industries. Some of his sample Client Dashboards are for logistics and high-volume manufacturing companies – where he has utilized multiple data sources (SQL, Excel & internal systems) and built Spotfire reports as a replacement of current tabular system.
