1. Transpose overview
Welcome to chapter two, where we're going to take a look at how the transpose tool is laid out as well as some scenarios that would work well for it.
2. Pivoting columns into rows
Sometimes we receive data in a shape that doesn't work for our reporting needs. Here, we can see that we have State, which contains Texas and California. We also have each month of quarter one as a column, listed as Jan, Feb, and Mar. This might be a good format for a specific request, but will perform poorly when loaded into a business intelligence tool like Tableau or PowerBI.
3. Pivoting columns into rows
We can use the transpose tool to pivot these columns into rows. Here, we will keep state as a key column, and select Jan, Feb, and Mar as data columns.
4. Pivoting columns into rows
The data has gone from a wide table to a long table. These long tables are helpful for storing multiple metrics or dimensions and their associated values. Rather than adding a column each time a new metric or dimension is needed, a row can be added, and processing time and performance are minimally impacted. Not only this, but the schema and structure of the data remains unchanged. It is much easier to manage a table like this in the long run.
5. Pivoting columns into rows
To figure out the number of rows you end up with in the new long table after transposing, we take the number of initial rows multiplied by the number of Data Columns selected. If we do that math here, we had 2 rows times 3 data columns, which comes out to 6 rows.
6. Renaming with the select tool
It's also very common to rename the output columns from the transpose tool using a select tool immediately following. It makes your final dataset much clearer than the default 'name' and 'value'
7. Dropping columns and mixing data types
Sometimes we have datasets that are very wide, containing many columns. Let's take a couple rows from our previous table, which contained State and Sales. For this example, we will expand the table to 7 columns. If we want to leave some of those columns out when we pivot, we can do that with the transpose tool! You can see the columns we want to leave out marked in grey. Blue columns will be key columns, and orange columns will be data columns.
8. Dropping columns and mixing data types
Let's see if we can clean this up using Order ID and Customer Name as key columns, and Sales and State as data columns. This means the key columns which are in blue will be unchanged, the data columns which are in orange will be pivoted to Name and Value columns, and the unwanted columns which are in grey will drop from the dataset! Because State is a string and Sales is numeric, we will be mixing data types in our Value column. Also, bear in mind that the Transpose tool will automatically use whatever the shortest type that will accommodate the data is, and mixed data types will result in a string.
9. Dropping columns and mixing data types
Here is our new table, with multiple metrics tucked into Name and Value columns. You can see the math once again works as 2 rows times 2 data columns results in 4 rows. The dropped columns have been excluded from this visual. We've also shown the rename with the Select tool here.
10. Dropping columns and mixing data types
To recap, we have successfully pivoted columns, dropped columns, mixed data types in the value column, and created tables with multiple key columns as well. One quick note is that the Transpose tool does not require any key columns to be selected. This will result in just name and value columns. All of these operations can be done with the transpose tool. Now you're ready for analysis!
11. Let's practice!
Over to you now! In the next exercise, you'll determine how many columns the transposed output will have.