Preppin' Data

Solution by Tom Prowse and you can download the workflow here . Step 1 - Create KMs, Turbo Trainer & Outdoors Fields The task this week is to input the data and then convert the value field to kilometres. From the requirements we know that Carl cycles at an average of 30km/h so we can use the following calculation to convert the values, that are in mins, to km's:  KMs   ZN(IF [Measure] = 'min' THEN ([Value]/60)*30 ELSE [Value] END)    On a new step we can then convert the measure field by changing mins to 'Turbo Trainer' and kms to 'Outdoors'. We can do this by double clicking on the value in the profile pane and then renaming for each value.  Finally we want to use a rows to columns pivot so that we create a new field for Turbo Trainer and Outdoors:  After the pivot our data should now look like this:  Step 2 - Create New Rows Now we want to ensure that we have a row for each date between the 1st Jan and 1st Nov 2021, therefore we need to create s...

 Created by: Carl Allchin This week's challenge is helping me to understand how little fitness I have actually done this year. I'm going to blame writing a book, having a baby and running Preppin' Data but let's quantify my laziness shall we? Input I record all of my cycling data in a google sheet that I've downloaded for you all to prep. However, I record different types of cycling so some conversion will be necessary but we'll get to that shortly.  There is just one file to use this week. Requirements Input the data Convert the Value field to just be Kilometres ridden  Carl cycles at an average of 30 kilometres per hour whenever he is measuring his sessions in minutes Create a field called measure to convert KM measurements into 'Outdoors' and any measurement in 'mins' as 'Turbo Trainer'. Create a separate column for Outdoors and Turbo Trainer (indoor static bike values Ensure there is a row for each date between 1st Jan 2021 and 1st N...

Solution by Tom Prowse and you can download the workflow here .  This week's challenge comes from Beth Kairys who showed us some of the fundamental data prep techniques at the latest Tableau Prep User Group . The overall aim of the challenge is to get our cases data into a nice structure so that we can easily make a waterfall chart in Tableau Desktop.  Step 1 - Date Lodged The first step is to input the Business Unit A table and create the Date Lodged field. Currently, this is split out in a Year, Month, & Day field, therefore we need to combine these into a single field using the following calculation:  Date Lodged   MAKEDATE([Year], [Month], [Date]) Now we have the new date field we can remove the Year, Month, & Day fields from the table.  Step 2 - Update Risk Rating Next we want to use the Risk Level table to update the risk rating. We need to input the Risk Level table and then join this to our flow using Rating = Risk level, and we want to include ...

 Challenge by: Beth Kairys A couple of weeks ago, at the Tableau Prep User Group, co-leader Beth Kairys presented a brilliant workflow which covers a lot of the fundamentals of data prep. Luckily, she agreed to allow us to turn it into a Preppin' Data challenge, thanks Beth! If you missed the session or want a sneaky peak at the solution, then click here to watch the user group recording. The purpose of the challenge is to get our cases data into a format where we can reproduce the following chart (which you're welcome to do as a bonus exercise too!) Inputs There are 3 inputs for this challenge: Business Unit A cases  Business Unit B cases  Risk Level lookup table  Requirements Input the data From the Business Unit A Input, create a Date Lodged field Use the lookup table to update the risk rating Bring Business Unit A & B together We want to classify each case in relation to the beginning of the quarter (01/10/21): Opening cases = if the case was lodged before...

  Solution by Tom Prowse and you can download the workflow here.  This week's challenge took a return to the Allchains Bike Store for another challenge using the create new rows feature from the latest Tableau Prep release. The challenged focused on how we can make an estimation of how much money had been raised from a charity fundraiser where the data hadn't been recorded accurately. Step 1 - Create New Rows The first step is to input the data and then we can use the create new rows step to fill in any date that is missing between the first and last date in the data set.  The setup required to create these new rows is as follows:  We can use the values from the Date field, where the start date is the earliest date and the end date is the latest date, and we can update the existing fields when adding the new rows. This should be on a 1 day increment and the new rows should copy the values from the previous row.  Our updated table should look like this:  St...

 Challenge by: Carl Allchin This week we are going to use one of the newer features in Tableau Prep, New Rows. New Rows was released in Prep Builder version 2021.3 so you will need that version or later of Prep for this challenge (unless you want to scaffold your data yourself).  To date, Tableau Prep has assessed each row of data separately but we all know data can often be incomplete and that made for some tough challenges. With the New Rows step within Prep Builder this should make working with incomplete data sets much easier (and in some cases possible where it wasn't before).  If you want to learn more about the New Rows step and how to use it, have a look at this 'How to...' post on our new site: https://www.preppindata.com/howto/how-to-create-new-rows This challenge isn't just for Prep users. Jenny, Tom and I love to see solutions on all different types of software and in different languages so please feel free to use whatever you want to as it helps everyone...

Solution by Tom Prowse and you can download the solution here. This week we are looking at football data with a focus on the historical results for Southend United. Southend had only won 18 times in their last 108 games, so we look back at the historical results which were a lot more promising compared to recent times.  Step 1 - Tidy Input The first task after inputting the data is to clean some of the fields, including renaming the P1 field to Pts and excluding any null rows. We can rename the P1 field right in the input step by double clicking on P1 and then renaming to Pts We can use a filter to remove any of the Null values from the Season field, and our table should now look like this:  Next we can create the special circumstances field using the following calculation:  Special Circumstances IF ISNULL([POS]) THEN "Incomplete" ELSEIF [POS]="ABAND" THEN "Abandoned due to WW2" ELSE "N/A" END Then the final part of initial cleaning is to make s...