2023: Week 18 - Solution

Solution by Tom Prowse and you can download the workflow here. 

This week's challenge takes us back to the fundamental steps within Tableau Prep - Input, Clean, Pivot, Aggregate, and Output. These are the most common steps used within Tableau Prep and are all vital when preparing data.

Step 1 - Location Names

After connecting to the 'Messy Nut House Data' as a text file we can drag in the single sheet as our input. Then we create a new clean step where we can tidy the spelling mistakes within the Location field. 

To do this we can use some of the clever group features within Tableau Prep to group values by Spelling or Pronunciation.

From here we can manually rename the fields so that we have three cities - Liverpool, London, and Manchester. 

Step 2 - Reshape the Table

Next we want to reshape the table so that each of the Categories become a field name. To do this we want to pivot the data using a Rows to Columns pivot where we group by Category and Sum the Value field.

As a result we now have three additional fields where the Category field has been replaced by the 3 separate values - Weight (kg), Price (£) per Pack, and Quant per Q.

Step 3 - Calculate Total Revenue & Avg Price

Now we have separate fields for each of the different categories, we can now go and calculate the revenue for each product in each store. For revenue we want to create a calculated field which will work at a row level and multiply the Price by Quantity: 

Revenue 

[Price (£) per pack]*[Quant per Q]

Once we have the Revenue on each row, we can then use an aggregation tool to find the total revenue and avg price per location. To do this we want to group by Location, and then Sum Revenue and Avg Price.

Then finally we can clean the Avg Price field by rounding the number to 2 decimal places using a Round function: 

Avg. Price (£) per pack 

ROUND([Price (£) per pack],2)

We are then ready to output our table that should look like this: 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!