2022: Week 18 - Solution

Solution by Tom Prowse and you can download the workflow here. 

This week's challenge is focussed on cleaning a pivot table that has a lot of fields (145!!). Although this might be good to look at in a Spreadsheet, it's not the best way of structuring our data in Tableau so let's look at how we can 'un-pivot' our data.

Step 1 - Rename Fields

First up we want to dynamically rename our fields so that there is a common separator between the Bike Type, Date, and Measure. 

Now this could be a long manual task as there are 145 fields so it would take a seriously long time for us to go through each one. Luckily, Tableau Prep has a feature that allows us to rename our fields all in one go!

Using the 'Rename Fields' feature we can replace the '___' with a single '-':

This allows us to create the replace the separator between the Type and Date. 

We can then repeat this process by replacing '_' with ' ' (space):

At this stage our fields should now all have a similar structure, with the common separator fields throughout: 

Step 2 - Pivot Data

The next step is to pivot our data. We want to bring each of the columns into rows so using the columns to rows pivot where we include all of our Type, Date, Measure fields. 

As we have so many fields, this could be a problem when trying to select all the fields. Instead of selecting them all, we can use the Wildcard Pivot to dynamically bring all of the fields into the pivot. This is where our separators can come in handy as we use the '-' as our matching pattern: 

After the pivot our data should now look like this: 

Step 3 - Split Type, Date, & Measure

Next we want to break apart our Type, Date, and Measure field so that they are separate fields in our table. 

We can do this by using a custom split, to split all values using the '-' as a separator: 

This splits the values into three separate columns that we can rename accordingly, and making sure that we change the Month field to be a date. As a result our table should now look like this:

Step 4 - Sales & Profit Field

The final step this week is to create separate fields for the Sales & Profit measures. This involves us re-pivoting our data again but this time we want to take the rows and create columns, therefore we use a 'Rows to Columns' pivot. 

The rows to columns has a slightly different setup, so we need to provide a field that we want to become our new columns and also a measure to go into those columns. 

Therefore, in our example we want to use the Measure Name in the Field to pivot and the Values in the Measure to aggregate: 

After this we are ready to output our data that looks like this and is much easier to work with: 

You can download the full output here. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!