1. Using the transpose tool in a workflow
Before we dive into the transpose tool, let’s have a look at how the data is currently structured. We’ve got a strange layout on our hands, and we’ve been asked to clean it up for a data visualization tool. This data is too wide, and we have a lot of null values. Let’s see if we can get it looking a little cleaner.
First I’ll drag out a transpose tool and connect it to the dataset. Now, as we’ve covered so far, we will have to decide how to configure each of the three areas within the transpose tool: key columns, data columns, and missing columns. In key columns, we are deciding which columns we want to keep from the existing dataset. We want to keep region and pivot all the state columns. Therefore, we will select only region in the key columns dropdown.
In the data columns dropdown, all fields were selected automatically. However, when we selected region in key columns, it was deselected in this section. This works great - all the state columns will be pivoted.
Lastly, we need to decide what to do with missing columns. These are columns that may be missing due to a data update in a monthly file for example, not missing values in a table. Here we will leave it on warn.
Based on how we have configured this transpose tool, we will retain the region column and have name and value columns containing state names in the name and the associated values in the value. If we click run, we can see if we get what we expect.
Our transpose was successful. Now, we just have a few small issues to fix. First, we’ll filter out nulls in the value column. Secondly, we want to rename our columns. Lastly, we want the data sorted alphabetically - first by region, then by state. Let’s start with the filter. It never hurts to begin with the smallest dataset possible!
We can drag a filter tool from the preparation palette and place it immediately after our transpose tool. All this takes is a basic filter. We will select Value and then select ‘is not null’. Let’s run this and check our T branch to see if it worked! We can see that it did.
Next, we can add a select tool from the preparation palette and attach it to the T branch. This tool will allow us to change the names of our Name and Value columns to State and Sales, respectively. We can click run.
Lastly, we will add a sort tool after the select tool. We can choose which columns to sort by: Region, Ascending, and State, Ascending. We will put them in that order. Let’s click run and double-check our results. This is looking good!
We now have our data filtered, renamed, and sorted.
Now it’s your turn - let’s do some transposing!
2. Let's practice!