Challenge Yourself to Clean Data in a few Clicks

One of the great things about Tableau Prep is that much of the functionality can be achieved through clicks of your mouse, rather than having to write complex calculations. This makes it a great tool for those just starting their journey in data preparation.

Preppin' Data has made an effort to showcase this amazing functionality by creating the below challenges. As well as the usual requirements, you are also forbidden from using calculated fields. Make sure you have a go and see how rewarding self service data prep can be, before diving into the video solutions.

Preppin' Data 2019: Week 26 - Clean Cocktail Data with only Clicks

This challenge starts with a cocktail menu. We split out the ingredients list and reshape the data so that each ingredient has its own line. Then we work out the average price of a cocktail containing that ingredient. Finally, we join it back to the cocktail menu data to find out which cocktails containing your favourite ingredient offer the best value for money!

Preppin' Data 2019: Week 42 - Discover the new store location without a single calculation

For this challenge, we clean the data by exploring the three dots in the top right corner of each field and making use of handy light bulb recommendations. Then we explore how joins can be used not only to bring in new information, but also how they can be used to filter out rows of data which are not relevant to us. 

Preppin' Data 2020: Week 8 - When are Chin & Beards Suds Co missing budget and exceeding profit?

This first part of this challenge explores how useful wildcard unions can be when bringing in our data. Although they do also require us to do a bit of troubleshooting when column names change for different weeks. We also explore how useful the Data Interpreter can be when Excel users place multiple tables in the same sheet. Finally, we see how Join Clauses can help us only bring through data where sales have exceeded the minimum profit targets.

The second part of this solution looks into the difficulty of joining together two tables with similar information, but different data structures. Pivots can really help us out in these situations! We then consider how best to only return information about where we're missing either our budget for value or budget for volume. The first step is to find out where we're exceeding budget in both measures and then use a left join to exclude these values from out dataset. 

Preppin' Data 2020: Week 17 - Netflix Usage

The first part of this challenge looks at what devices people are using to watch Netflix. Our first step is to use an Aggregate step to deal with some duplication issues. Then, we need to use the splitting functionality to extract information about those who have watched on multiple devices. This leads to needing to do some reshaping of our data to have a line per device. We join on a lookup table so that we're only focusing on certain devices, labelling all other device types as "Other". Finally, we use an Aggregate to count how many are watching on each device type. 

The second part of the solution looks at what shows people have been watching and their ratings. We need to use that splitting and pivoting logic again so that we have a show per line. The Netflix Shows data set allows us to determine which shows entered in the "Other" column are valid. Then we need to reshape and clean up our ratings. There's some work to be done on getting shows in the "Other" column to join to their ratings, but some clever join logic allows us to do this. Finally, we return an average rating per show and rank them in descending order. 

Preppin' Data 2020: Week 44 - Halloween Costume Clean Up

This challenge is a little spooky, as we have Halloween costumes from around the world in their native languages! Luckily the group and replace functionality in Tableau Prep is great for grouping them into their English translation. Next, we notice some spelling errors in some countries when we change the Country field to have a Country data role. Prep's recommendations come in really handy for fixing this dynamically. Next up we use the new (2020.3) Fiscal Dates feature to turn our Date field into the Fiscal Year. 

Then comes the part of the challenge where we start to reshape our data, bringing all the Sales fields into one column. A little bit of clean up is needed to separate the Currency from the Sales Value. Finally we pivot once more to have a field for Sales in each Fiscal Year.