How to pivot rows to columns in Tableau Prep | #PreppinData | 2019 | 02

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

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:

Screen Shot 2019-02-21 at 3.43.22 PM

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

Screen Shot 2019-02-21 at 3.48.38 PM

GASP!!!!

shocked
No this won’t do at all.
We need to turn on the Data Interpreter
The data interpreter is really great for those excel files that are formatted like dashboards or scorecards with lots of blank rows and columns used as spacers or page breaks
After the data interpreter does it’s magic we can see that Prep created three different possible inputs
Screen Shot 2019-02-21 at 3.58.37 PM

 

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

Screen Shot 2019-02-21 at 3.59.58 PM

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

Screen Shot 2019-02-21 at 4.20.20 PM.png

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

Screen Shot 2019-02-22 at 10.49.32 AM

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.

Screen Shot 2019-02-22 at 10.53.33 AM

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”

Screen Shot 2019-02-22 at 11.01.28 AM

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

Screen Shot 2019-02-22 at 11.06.08 AM

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

Screen Shot 2019-02-22 at 2.45.41 PM

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

Screen Shot 2019-02-22 at 2.50.47 PM

GO FORTH AND PREP!

0 comments on “How to pivot rows to columns in Tableau Prep | #PreppinData | 2019 | 02”

  1. sriram Reply

    Sean can you explain the steps to make the final dataset to be arranged like dated from 16 to 22 for Edinburgh than followed by London with date from 16 to 22.

  2. sriram Reply

    sean Help me out to make the city and date to be arranged as Edinburgh followed by London and the data from 16 to 22 for each of them.

Leave A Reply

Your email address will not be published.