Preppin' Data

Challenge by Tom Prowse The latest version of Tableau Prep was released this week! This included a great new feature that allows you to generate new rows that aren't already available within your data set. If you want to know more then check out Carl's blog post where he has this covered! As there's a new feature that makes our lives that little bit easier, I thought it would be a great opportunity to revisit one of the older challenges to see how things have changed. Therefore this week we are nearly going all the way back to where Preppin' Data began... Challenge 2019 Week 3!  If you haven't completed this challenge then you can find the original post here , but for the challenge we are going to calculate the recurring revenue based on a mobile phone contract length. This can be completed in the new version of Prep (2021.3 onwards) or an older version if you haven't had the opportunity to download it yet (better yet, why not try both!).  Scenario You work for

  Solution by Tom Prowse and you can download the workflow here.  There have been some big changes to our lives in the past couple of years, so this week we looked back at what people have been searching for on Google and how this has changed.  Step 1 - Average Index First we are going to focus on the Timeline data table, where we want to calculate the overall average index for each search term. Before we can calculate the average, we need to transform the table by pivoting the main search terms from columns to rows. We can use a Wildcard pivot for this by using the matching pattern 'World':  After the pivot, we have the search terms in a single column but we need to do some tidying to remove the 'Worldwide' part. This can be easily done with an automatic split, or by using a custom split to return the first occurrence before the ':' separator.  Once we have tidied and renamed some of the fields, our table should look like this: Now we have the table in this fo

 Challenge By: Jenny Martin For this week's challenge, I wanted to use Google Trends to take a look back over the past couple of years and see what people were searching for. In particular, are these categories still as popular now as they were in peak lockdown? How does the experience vary around the world? We'll be looking at: Pet adoption (who didn't want a furry work from home buddy?!) Online streamer (can one make money from playing video games?) Staycations (everyone's favourite word, right?) Inputs There are 2 inputs this week: Timeline - indexes for how popular the term is  Country Breakdown - the percentage of these terms popularity in each country  Requirements Input the data Calculate the overall average index for each search term Work out the earliest peak for each of these search terms For each year (1st September - 31st August), calculate the average index Classify each search term as either a Lockdown Fad or Still Trendy based on whether the average inde

 Solution by Tom Prowse and you can download the workflow here.  This week the challenge was inspired by Jenny putting some pictures up in her house and it just goes to show that a Preppin' Data challenge can really come from anywhere! Step 1 - Split Widths & Lengths First we need to split apart the width and lengths for both the frames and picture sizes. The sizes are a mixture of different formats and measurements so let focus on the Pictures table first.  Within the pictures table we have all of the measurements in CMs so we don't need to make an conversions. However, we do have one measurement which is a square (24cm2) so this has 24cm as the height and width.  To deal with this, we can first we want to split length from the start of the string. There isn't a clear separator to do this easily, however using the Automatic split will give us the required numbers that we need from either side of the 'x'. This provides us with a separate field for both the heig

 Challenge By: Jenny Martin Data preppin' ideas really are all around! I was hanging some pictures the other week and I had some weird and wonderful sizes, so working out the perfect frames was a bit tiresome. If only I could use a data preppin' tool to speed up the process...  Inputs We have 2 inputs this week: Picture sizes  Frame sizes    Requirements Input the data Split up the sizes of the pictures and the frames into lengths and widths Remember an inch is 2.54cm Frames can always be rotated, so make sure you know which is the min/max side See which pictures fit into which frames Work out the area of the frame vs the area of the picture and choose the frame with the smallest excess Output the data Output 4 fields Picture Frame Max Side Min Side 14 rows (15 including headers) 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  &  @TomPro

  Solution by Tom Prowse and you can download the workflow here .  This week was the final instalment of the Excel challenges month, so it seemed like the right time to involve a Vlookup and Index matches as these are such popular features. For the challenge this week we want to compare monthly targets with data stored on another sheet... let's see how we solved it! Step 1 - Average Monthly Sales The first step this week is to input the Employee Sales table and then calculate the average monthly sales for each employee.  Before using an aggregate tool to calculate the average, we need to pivot our data so that we have all of the months in a single column, therefore we can use a wildcard columns to rows pivot to bring all of the months through:  Now we have a single column for sales and months, therefore we can use the aggregation tool to calculate the average monthly sales per employee: Our table should now look like this:  Step 2 - Combine Targets & Sales We can now focus on t