Analytics anyone can use.
Data prep anyone can use.
Analytics for organizations.
Cloud analytics for organizations
People-friendly data is often captured and recorded in a wide format with many columns. Machine-readable data is better in a tall format, with fewer columns and more rows.
Tableau prefers data to be formatted in a machine-readable format. To reshape your data for easier analysis in Tableau, you can perform a pivot. In Tableau, pivoting means transposing data from a crosstab format into a columnar format–from wide, short tables into thin, tall tables. Tableau Prep makes pivoting visual, allowing you to see how your data changes with every step. In this post, we’ll show how to perform multiple pivots in Tableau Prep to shape your data for analysis.
The original data.
When we analyze data in Tableau, we need it to be tidy. Each column should have a single unique meaning. So what do you do when your field meanings aren’t unique? When multiple columns means the same thing, you can use pivot. But what if you have multiple groups fields?
For example, in this data set, there are three different bestseller lists: Young Adult, Hardcover Fiction, and Paperback Nonfiction. However, the data is structured in a way that there are two columns per list, one for author and one for title. Therefore, there are three columns containing information about authors and three columns for titles. When there are multiple columns containing the same information, this is a sign the data should be pivoted.
For analysis, the ideal structure for this data would be individual columns for: Rank, Author, Title, and Bestseller List.
The image above shows the desired end state.
However, if we pivot the data as it is, we wind up with author information interspersed in the same column as title information, rather than a column for each piece of information. We need to pivot both author and title information, but we can’t pivot them together.
If the data is pivoted simultaneously, author and title information is interspersed in the same column.
To get around this, we need to perform coordinated pivots. Essentially, we need to pivot the columns containing author information and do a separate pivot for the columns containing title information. Then we need to rejoin those columns back together.
In Tableau Prep, we’ll do this by branching the flow so we can work with author and title data separately.
Let’s go through it piece by piece.
This data is ready for analysis in Tableau! We can add an output step and run the flow to generate the cleaned data set.
The final output.