Tableau Prep Builder is Tableau's data preparation and transformation program, which is useful for reshaping your data before visualization. This month of training we have the benefit of being coached by Carl Allchin, who literally "wrote the book" on Tableau Prep.

With his expertise comes fascinating challenges that push the limits of what Tableau Prep, and data transformation software in general, is capable of. Yesterday, we were presented with a tiny dataset, consisting only of the numbers 1 and 9:

We were challenged to convert it into a full times table:

This required a creative combination of joins and pivots, but I won't give it away entirely since it is actually a Preppin' Data challenge that can be found here (https://preppindata.blogspot.com/2022/10/2022-week-40-times-tables.html).

Instead, in this blog I am going to propose and solve a similar challenge of my own creation that has the same feeling of impossibility. The goal is to identify every number between 1 and 1000 that is divisible by 37. This is a similarly mathematical task to the Preppin' Data challenge. Unfortunately, for the purposes of my task I have removed almost all of the numbers between 1 and 1000, leaving you with only:

This dataset has one field, one row, and one cell, containing one character. It is pretty much the smallest non-empty dataset possible.

If you are interested in solving this for yourself and comparing your solution to mine STOP HERE, otherwise continue reading for my methodology.

My Solution

The first step I needed to take was to get to multiple data values from one. In order to do this, I split off into a clean step to create a duplicate of the data, then joined this new version with the original.

Now the data has two columns, essentially adding a duplicate field to the original. For the purposes of the rest of the solution, it would be more ideal to have our data in rows, so my next step is to pivot the data, taking it from

to a long version –

using a pivot step in Tableau Prep.

We now have a long form dataset with multiple values, but we still have not incorporated the 1000 value that is central to our task. To do this, I created a calculated field called Value in a clean step.

The idea of this calculation was to preserve the original 1 as 1, but replace the duplicate 1 with 1000:

Next, using a new rows step, I generated a row for each of the values between 1 and 1000 to filter to get only the multiples of 37. We have finally arrived at a data structure that allows us to actually address the initial question! Using a clean step, I applied a filter using the formula: [Value] / 37 = INT([Value] / 37), which checks if a given integer divided by 37 is also an integer (which is equivalent to the INT() function returning the same value).

Returning the TRUE values from this filter, we get the desired output! Even though this was a challenge of my own creation, it was interesting, fun, and stretched my understanding of what Tableau Prep is capable of!