Preppin' Data

 Created by: Carl Allchin The football (soccer) season is upon us. The challenge this week is to take the game results and make them ready for analysis. Sadly, when you google Premier League results the data is not structured in an analytically friendly way.  We've taken a copy of all the results from last season, can you prepare it for analysis? Input One Excel file with a single worksheet: Requirements Input the dataset Add in a row number as at source (this will help for next week's challenge) Create a column to show which Matchday (ie which game in the 38 game season) each game occured in Remove any rows of the data set that don't contain game information It's helpful to put all the match information in one column rather than the two columns in the input Replace the new row character (\n) with a different character(s) (I use two pipe characters: ||)  \n is recognised as char(10) by Prep Builder Form separate columns for: Date Home Score Home Team Away Score Away Te...

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Last Friday After we have replaced the data set from last week's workflow, first we need to add a calculation to the Last Friday step before the join. This will help us to identify dates to roll back:  Last Friday IF [Ends]<[Last Friday] THEN date(dateadd('day',-3,[Ends])) ELSE [Last Friday] END Step 2 - Holiday Overrule We also need to add an additional clean step after the join where we can calculate whether to overrule the holiday or not. For this we need to find the minimum term? for each cake day using a fixed LOD:  Holiday Overrule   We can then use this in a filter to return only the values that are equal to the Term? -  [Holiday overrule]=[Term?] This allows us to remove any duplicate fields from the table.  These are the only changes that we need to make to our original workflow so we are ready to output the data that should look like this:  You can view the output...

Challenge by: Jenny Martin What's this? Our first 3 part challenge! Whilst the Prep School were thrilled with the revised Birthday Cake workflow we produced, they realised there had been an error in missing out one of the Bank Holidays that occur during term time.  This holiday is the Early May Bank Holiday, taking place on 05/05/2025. This occurs during the Summer, term 5. From last week's solution, anyone who has a birthday on the 5th May is scheduled to have a cake on a public holiday, so we must adapt our workflow to fix this! Inputs You can adjust your workflow from last week to meet this new requirement, or download our solution from last week and adapt that.  The original dataset of student's dates of birth remains unchanged: 2022 Week 2 Input The dataset detailing terms and holiday dates, updated to include the 5th May bank holiday.  2024 Week 34 Input Requirements Update the input to use the 2024 Week 34 input Ensure that any birthdays on 5th May are rolled back...

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Student's Birthday We first want to determine when each student's birthday for this school year. For this we can use the input from the 2022 Week 1 challenge and calculate this year's birthday:  This Year's Birthday   IF MONTH([Date of Birth]) >=9  THEN MAKEDATE(2024,MONTH([Date of Birth]),DAY([Date of Birth])) ELSE MAKEDATE(2025,MONTH([Date of Birth]),DAY([Date of Birth])) END We can then calculate their 'cake day' by duplicating this field and converting it to 'day of the week' using the in-built Tableau Prep features  After renaming this field we can then calculate the cake day:  Cake Day IF [Birthday Day of Week] = 'Saturday'  THEN DATE(DATEADD('day',-1,[This Year's Birthday])) ELSEIF [Birthday Day of Week] = 'Sunday'  THEN DATE(DATEADD('day',-2,[This Year's Birthday])) ELSE [This Year's Birthday] END At this stage our table should...

 Created by: Carl Allchin When teaching at The Data School, I love it when people ask a question that spurs a Preppin' Data challenge.  This week we'll use the same input dataset as we first used in the early weeks of 2022. This time we're looking at adding in the school term dates to further the initial birthday cake challenge. If you want to go back and do the original challenge then there is a link here . This challenge is based on the School Year dates of schools in the county of Essex in the UK. 'Terms' describe when children go to school. Inputs There are two inputs this week. One dates back to the 2022 challenge and a new data set to enable you to complete the challenge: 2022 Week 2 Input 2024 Week 33 Input Requirements Input the data sets  Determine each student's birthday for this school year The school year starts on 1st September and ends on 31st August the following year Create a Cake Day field that shows when the school needs to buy each student a c...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Combine Cards, Pokémon, and Trainer Cards First we want to input our tables and combine them together to make a single table. For this we need to make some changes to the tables to ensure that they combine correctly and so we don't have duplicate values.  First we can look at the Cards input, where we want to split the Leader and the Pokémon fields from the Name field. The name field should become two fields in the format of: [Name]'s [Pokémon]  Normally, a custom split will use the 's as a separator however in this case using the ' doesn't work so therefore we can write out two separate calculations instead:  Leader TRIM(SPLIT([name],"'s",1)) Pokémon   TRIM(SPLIT([name],"'s",2)) The Pokémon Cards table will now look like this with the additional fields:  Now we can focus on the 2nd input which is the Pokémon table. For this we need to ensure that we have a single ro...

Challenge by: Jenny Martin I recently got back into Pokémon card collecting, which not only involves collecting new sets, but also going through old sets I had as a child and organising those. For this challenge we'll be focusing on 2 really cool old sets: Gym Heroes and Gym Challenge. They contain cards of Pokémon that belong to particular gym leaders in the Kanto region, such as Blaine's Vulpix, one of my personal favourites: Image taken from Pokéllector Now, traditionally, you would organise cards using the number in the bottom right corner e.g. 65/132 in the above example. But for these sets, I wanted to organise them all: based on which gym leader they belong to in the order of the Kanto gyms  with the Gym Leader card first followed by their Gym card followed by the remaining Trainer cards associated with them (in card number order) followed by their Pokémon in Pokédex order using the Pokémon level in the bottom left corner of the card, where a leader has multiple of the s...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Fill Down Fields Within the input step we can rename the fields in the Results table as per the requirements. This includes making the fields sentence case and removing the additional 800 field. We can then fill in the null values with the actual position for each athlete. For this we want to use the Fill Down calculation where we order by the source row number and compute using the Position:  We can then remove the original position field and rename our new field to Position. We can then repeat the same with the Athlete, Nationality, and Total Points fields:  Athlete Nationality   Total Points   At this stage the table will look like this:  Step 2 - Total Points We now want to remove any letters from the Total Points field. We can do this by using the in-built functionality within Tableau Prep to remove letters and then trim spaces.  Now we have the total points as a number we can tra...