Here is my solution for the first Preppin’ Data challenge of 2020. The goal for this challenge was to calculate total profits for Level 1 and Level 2 of the profit statement’s hierarchy and add leading spaces to visually highlight different levels of this hierarchy (5 spaces for Level 2 items and 10 spaces for Level 3 items).

My solution in Tableau Prep

Original challenge and data set on Preppin’ Data’s blog.

## Step 1: Splitting the Item field

To calculate the totals for Level 1 (i.e. Bar Soap, Liquid Soap, Accessories) and Level 2 (i.e., Opaque, Transparent, Exfoliating Glove, and Soap Holder), I started with adding a Cleaning step after the input to split the Item field (using space as a delimiter) and get a new field called Index. Then, I split the Index field into three new fields (Level 1, Level 2 and Level 3) to use them in later steps for calculating totals for Levels 1 and 2.

## Step 2: Calculate total profits for Levels 1 and 2

I added two branches from the Cleaning step for two separate Aggregate steps: one for calculating the profit for Level 1 products, and another for the profit for Level 2 products.

For Level 1

For Level 2

## Step 3: Bring the totals to the main data set

After calculating total profits for Levels 1 and 2, I needed to bring them back to the main data set. To do so, I joined each Aggregate step back to the earlier Cleaning step, i.e. the main data set.

For Level 1

For Level 2

Once total profit numbers were added to the main data set, I added two new Cleaning steps and created calculated fields to move the totals for Levels 1 and 2 to the same Profit field.

For Level 1

For Level 2

## Step 4: Union two tables

Now I could bring these two updated tables back together to create the final profit statement. After adding the Union step, I filtered all null values from the Profit field as by now there were no null values I needed to keep.

Union of the two tables

## Step 5: Removing duplicates

The original profit statement had 31 rows, but now, after all the manipulations, there were 53 rows. I removed duplicate values by adding an Aggregate step and grouping all the fields in my data, except for the Number of Rows field generated by Tableau Prep. Now I was back to 31 rows.

Removing duplicates