So it looks like Carl Allchin and Jonathan Allenby‘s little Tableau Prep project had a great first week!

The solutions to #PreppinData Week 1 are available here: https://t.co/2oSmskhgxl We loved the feedback and alternate solutions found (along with some visualisations too). The new challenge will be posted tomorrow! Thanks to those that filled in the participation google form. pic.twitter.com/rMM4Q9AVsj

— Carl Allchin (@Datajedininja) February 19, 2019

I certainly enjoyed it! And they’re back for another challenge this week with a few extra twists.

As always, please check out their website and get involved. Here are the reqs:

Alright let’s load up the data in Tableau Prep and get to Preppin’

GASP!!!!

let’s look at that first one, it looks promising and “most normal”

Nope, this won’t do either because Tableau Prep only removed the empty rows between the two sections. Let’s look at the other two inputs

Oh yeah, these will do nicely! Let’s union them together and then remove the table name column

And we don’t have any mismatched fields and no null values. We’re doing great. Let’s clean up the city column next

One of the [many] great things about Tableau Prep is all the smart features and the way that Tableau can analyze all the members of a dimension and group those values together is invaluable if you deal with a lot of free text fields.

If we select the [City] field and click the ellipses one of the options is “group and replace” and there are several options. We can quickly see that a majority of the values are spelled similarly with slight variations. Let’s see what Tableau Prep can do when we select to group by spelling

Wow, look at that! It took all 12 variations and grouped them into 2 values and it even knew what the correct spelling should be.

Now to get that last one included we just we need to multi-select that value and the value we want to group it with and click “Group Values” After doing that, we can go ahead and remove the [Measure] field as we won’t be needing it in the final output

Now the next requirement is to pivot the rows to create a new column for each metric.

Remember last week we pivoted columns to rows but this time we need to do the opposite (unpivot?) and luckily that’s as easy as just a couple clicks. Let’s add a pivot step and change the option to “Rows to Columns”

Now we’ll drag the column that we want to be columns and drag the column we want to aggregate.

Let’s take a look at our output and rename our new measure fields to include the units

And we can see in the top left corner that we have 14 rows and 6 columns which is what was required in the original challenge. We can also see that we don’t have any null values which is also important.

I didn’t have time this week to throw a viz together. But here is a screenshot of my complete Tableau Prep flow

GO FORTH AND PREP!