Tableau Prep can be a useful tool for cleaning and transforming data to make it more user friendly when it comes time to making a vizualisation.

While not as expansive as Alteryx, Tableau Prep can complete some useful and commonly used methods of data transformation. One in particular is pivoting data.

This allows you to transform data from vertical data to pivoted, or wide data or to unpivot data from wide format to vertical. It can make aggregation and summarization easier, can allow for different levels of analysis and can help make data more suited for the particular tool or program you’re using.

In Alteryx the transpose tool is used to pivot the table from wide to tall, while cross tab is the opposite, changing the data from tall to wide.

Here’s how this is done in Tableau Prep:

Import your data source

It’s a good idea to drag a clean tool out to view the data and make any changes such as data types, calculated fields, filters etc.

Click on the plus button and select the Pivot Option. By default this is set to unpivot – so converting columns to rows.

Switch this from Rows to Columns by selecting the drop down option and then select what you want for the horizontal top heading. In this case we’ll choose Category so will drop that into the top pivoted fields section. You’ll then need to select a field to aggregate the new columns, so we’ll drag profit to the bottom section in the pivoted fields card and change the aggregation from Sum to Average by clicking on “SUM”.

Now the final result shows the average profit which was the aggregation we chose for each of the sub-categories which we selected to be the dimension chosen for the columns.

 

 

 

 

 

 

 

 

 

Tim Fawcett
Author: Tim Fawcett