Tableau Prep and #PreppinData 2024 week 4

#PreppinData 2024 week covered using join types, in this case to understand which seats aren’t chosen given a seating plan and booking data.

I had a preconceived idea of the solution here, as I’m used to using LEFT OUTER JOIN in SQL and then having a WHERE clause that returns rows where the result from the right hand table IS NULL. So I was expecting to have a join and then a filter in my flow. However, Tableau Prep has some additional join types that let you return entries where there are only values in the left table, only values in the right table, or even a “not inner” join for entries only in the left or right but not in both. I gave the left only option a go and it did the job nicely! Great how you can click on the segment of the venn diagram representation of the join to select the type too.

PreppinData week 4 solution using left only join


Extra left and right only join types in Tableau Prep

Tableau Prep and #PreppinData 2024 week 3

Two lots of Tableau Prep practice this week. A forum question (see end of post) and #PreppinData 2024 week 3. The challenge for #PreppinData was to join targets from a spreadsheet, with a sheet per quarter, to our previous sales figures. And then to calculate difference from target. Similar union and clean up steps to previous challenges to get to the point where there are two data sets to join, and where we have consistent fields in both (first letter of class, and a month number). Then the join is pretty straightforward:

Tableau prep solution for PreppinData week 3, showing join

The forum question involved duplicating a set of rows – once for each value in a comma separated list in one of the columns. And then filtering out any cases where a value in another column appeared in the list. What I found interesting about Tableau Prep in this case was that I can specify a wildcard search for the pivot (B below), but for the initial split whilst I can select “all” it does still hardcode the number of columns split out (A below). So one of the tasks would robustly handle the introduction of more values in the comma separated list, but the other task would not … I think. The workarounds I came across seemed to be to work out how many values you could have in the string and specify enough splits to handle that number. I wonder if that could be improved…


Tableau Prep and #PreppinData 2024 week 2

Week two of getting to grips with Tableau Prep and I decided to countinue with #PreppingData. The team of Carl Allchin, Jenny Martin and Tom Prowse do a great job of picking challenges that gradually introduce you to functionality. This week covered unions, aggregation and reshaping data using pivots. I was particularly interested in pivots, as that’s a frequent challenge people have on the Tableau forums where we talk about data prep being a good option.

A more complex flow this time, and it probably took around 45-60 minutes. Below is my finished flow. And following that I share some more thoughts on this weeks use of Tableau Prep in the form of a quick “I like, I wish, I wonder” retro. Spoiler alert, there was a wow moment for me this week!

Week 2 solution:

My PreppinData week 2 solution

I like:

  • I liked that I could copy and paste the first aggregation step and then change the type of aggregation and the description on the copy.
  • And there was a moment of genuine delight when I realised I could copy the pivot step and paste against a different input (with the same pattern). I loved that!
    Tableau Prep option to paste in a step

I wish:

  • The fields didn’t reorder between steps. This is probably my OCD talking though!
  • I could define a sort order for the output. It would make it easier to sense check the output (granted it is easy to open in Excel and sort though).

I wonder:

  • If I needed to aggregate before pivoting, as the pivot step allowed me to pick an aggregation?
  • if it would be valuable to have a quick create option for pivot “rows to columns”? It took me a while to spot the option to change the pivot direction (screenshot below), when my brain was looking for an “unpivot” option instead.
    Tableau Prep pivot option to switch between Rows to Columns and Coliumns to Rows

Getting started with Tableau Prep

I’ve been meaning to explore Tableau Prep for a while and finally took it for a test drive.

Many data professionals have experienced the need to prepare and clean data prior to analysis in tools like Tableau Desktop. Classic examples are: splitting data out of a single combined field; un-pivoting when each year of a measure is in a separate column; or maybe combining sales data from multiple differently formatted sources.

For me data preparation has usually been in our SQL Server / Data Warehouse environment, using SQL or enterprise integration and ETL tools like SSIS, WhereScape RED, or ADF & Databricks. For others it’s likely to have involved tidy up in Excel, a tool like EasyMorph, or even custom development in Python. But for analysts a number of these tools aren’t always accessible.

Tableau Prep is a tool that makes it easier and faster for analysts to combine, shape, and clean data for analysis within tools like Tableau. It provides a visual low-code environment for repeatable “flows” to get your data ship-shape. I’m often answering Tableau community forum questions with the comment that “yes you can do this in Desktop, but it would be easier in a data preparation stage”. Hence I’ve been keen to give it a go myself.

How did I get started? Three easy steps!

1. Downloaded Tableau Prep. You can download the free trial, then if you have a Creator license you can enter your license key. Chances are if you’re a Tableau Desktop user you have a Creator license.

2. Followed the Get Started with Tableau Prep Builder tutorial on the Tableau help site. I found that this took 1-2 hours, and was a nicely paced introduction to the UI and basic features. I like how it encouraged you to “give something a go” – there is always an undo button!

3. Completed the #PreppinData 2024 week 1 challenge. This took about 30 minutes as most of what I needed had been covered above!

PreppinData is one of the amazing projects that is run by, and for, Tableau community members. If you’ve participated in Makeover Monday, Workout Wednesday, Back to Viz Basics, or one of the other community projects, then the approach will be familiar to you. Each week the organisers post a challenge to social media and their site. The challenge comes with a currated input data set, some requirements for you to try in Tableau Prep (or other data prep tool of your choice), and the expected output. You can give the challenge a go, share you solution and see what other members of the community come up with. A great way to learn and practice!

Week 1 challenge – tidy and split some fake airline loyalty card data into two files ready for analysis.

My solution:

- One clean step to: split out the combined field using two custom splits (Flight Details on //, From-To on -); remove now unecesary fields; rename and fix up data types;  and replace 1/0 with Yes/No.

- Two clean steps to produce the two filtered lists (card holders and non-card holders).

- Two corresponding output steps to output the required CSV files.

Summary screenshot below. Impressions so far … loving the tool! So much neater and repeatable than a series of Excel sheets, and seemed more accessible for analysts than other tools I’ve used.

PreppinData 2024 week 1 solution summary showing flow and steps