Chapter 6: Pivoting Data

You may encounter a scenario where analyzing data is complicated by the way the data is structured. For example, you may prefer to have columns as rows or vice versa. For example, you may have a column in your dataset with a true/false value for each product category. However, your data visualization would be easier to achieve if you had a single column for the product category, with the row value indicating the category name. In this chapter, you'll learn how to pivot your data from columns to rows and vice versa. The goal of pivoting is to ensure your data has the optimal shape required for your downstream analytics goals, for example, creating a dashboard in Tableau Desktop. Mastering the pivot functionality is an essential tool in your data transformation skillset.

In this chapter, you'll find the following recipes to help you pivot your data for analytics:

• Pivoting columns to rows
• Pivoting columns to rows using wildcards
• Pivoting...

Technical requirements

To follow along with the recipes in this chapter, you will need Tableau Prep Builder.

The recipes in this chapter use sample data files that you can download from the book's GitHub repository: https://github.com/PacktPublishing/Tableau-Prep-Cookbook.

Pivoting columns to rows

Data is often produced by systems in what the engineers building the system thought was the most efficient manner. Rarely do data processing and storage systems store data with visualization in mind. Similarly, you may have data available that is appropriate for one type of visualization but not another. In this recipe, we'll look at a sales dataset. This dataset has sales revenue values per category. The categories are Electronics, Groceries, and Household Appliances. Each of the categories has its own column, which prevents us from easily making a line chart with overall revenue. To resolve this, we're going to pivot the data such that these three individual columns become a single Category column, and values are placed in a single Revenue column.

Getting ready

To follow along with this recipe, download the Sample Files 6.1 folder from this book's GitHub repository.

How to do it…

Start by opening the Sales Data.csv file from...

Pivoting columns to rows using wildcards

If your data is subject to changes over time, particularly the introduction of new columns, your flow may not produce the output expected or even produce an error. When scheduling a flow for recurring execution, it is important that you can rely on its execution being successful. One of the ways in which the Pivot function can achieve this goal is by using wildcards. Wildcards can be used to identify columns that need to be pivoted, based on a header pattern, rather than an exact match. In this recipe, we'll pivot columns to rows using wildcards.

Getting ready

To follow along with this recipe, download the Sample Files 6.2 folder from this book's GitHub repository.

How to do it…

Start by opening the SalesData.csv file from the Sample Files 6.2 folder in Tableau Prep, then follow these steps to pivot columns to rows using wildcards:

1. Add a Clean step to your flow, then expand the bottom pane to observe the...

Pivoting rows to columns

When preparing data that has been generated by transactional systems, you may encounter data structures that appear nonsensical from a reporting and analytics perspective. Take a sales order as an example. A sale may be for one or multiple items and the total sales amount may be affected by things such as a loyalty card, discount, referral code, and of course sales tax. Depending on which system you are working with, such information may be reported separately, that is, in columns. However, it's quite likely to see multiple rows in your dataset for the same transaction. In this recipe, we'll look at pivoting data from rows to columns, which will resolve any issues arising from such a data structure. Broadly, these steps are similar to pivoting columns to rows, with some important differences, as we'll see.

Getting ready

To follow along with this recipe, download the Sample Files 6.3 folder from this book's GitHub repository.

How...