Preppin' Data

 Challenge By: Jenny Martin Inspiration for Preppin' challenges can come from anywhere. I've recently moved into a block of flats and let me tell you, I spend a lot of time waiting for a lift (or elevator if you're across the pond). It got me pondering whether the lift is operating optimally. Is it better to stay on the floor that you drop passengers until the next time someone calls a lift, or should the lift return to the most common starting floor? Input There is one input this week, detailing the time of each trip the lift takes, including which floor the passengers enter the lift and which floor the passengers leave the lift.  For simplicity, assume that the lift does not stop mid-journey to pick up new passengers, but completes its current trip before starting a new one. Requirements Input the data Create a TripID field based on the time of day Assume all trips took place on 12th July 2021 Calculate how many floors the lift has to travel between trips The order of flo...

  Solution by Tom Prowse and you can download the workflow here . This week we joined up with Workout Wednesday crew to create a joint challenge that helped us to visualise the Tokyo 2020 Olympic event calendar. The first part of the challenge was to make sure that the data is structured in the correct way so that it makes it easier when we come to visualise at a later stage.  Step 1 - Format Date The first step is to correctly format our date time field by combining the Date and Time field. To do this we want to use the MakeDateTime function which will allow us to bring together both the Date and Time fields into a single field.  The date half of this calculation is easy, all we need to do is change the data type to a Date and this will automatically be picked up for us.  The time half is a little bit trickier. First we have some time fields that are 'xx', we need to replace these with a number in the same format as the other rows, eg '0:00'. Next, we want to split ...

Challenge by Tom Prowse with collaboration with the Workout Wednesday team! This week is time for our annual get together with Workout Wednesday for a joint challenge so that you can have a full data prep to visualisation solution.  Unfortunately the Olympics was postponed in 2020, so for last year's collaboration we looked at historical winners through the history of the games. However, this year, Japan 2020 is going ahead so we thought it would be the perfect time to create an event calendar to help us keep track of the events that we don't want to miss.  Inputs The data comes from the Olympics website . ( Note; this was taken on Wednesday 14th July so the schedule for some events may have changed since! ). You can download the data here .  1. Event Schedule A list of all the event dates, times and locations throughout the games 2. Venue Details A list of all of the different venue locations Requirements Input the Data   Create a correctly formatted DateTime fiel...

  Solution by Tom Prowse and you can download the workflow here .  The challenge this week was to look at all of the results from penalty shootouts in the Football World Cup and European Championships and then analyse how good or bad the teams have been.  Step 1 - Input Data The first task is to input both tables from the Excel document, one for the World Cup one for the Euro's.  These can be input via the wildcard union feature, but the field names don't quite match up directly, therefore I've decided to bring both tables in and then use a union tool.  As you can see some of the fields need to be merged and we can do this by using functionality in the union tool, by selecting a field and then choosing the + of the 2nd field that you want to be merged:  Step 2 - Clean Fields The next step is to do some general cleaning of the competition, dates and teams. The following changes have been made:  Competition To parse the competition from the Table Names f...

 Challenge by Tom Prowse. '55 years of hurt, Never stopped me dreaming!' It was another night of pain for England fans on Sunday evening when they lost yet another penalty shootout in the European Football Championship final. This seems like it has been a common outcome for a lot of the tournaments that England have taken part in over the years, but what does the data agree?  The challenge this week is to analyse the all of the penalty shootouts in the World Cup and European Championships (Euro's) since 1976. Input Data is from Wikipedia ( World Cup & Euro's ) and is two sheets:  World Cup Euro's Requirements Input Data Determine what competition each penalty was taken in Clean any fields, correctly format the date the penalty was taken, & group the two German countries (eg, West Germany & Germany) Rank the countries on the following:  Shootout win % (exclude teams who have never won a shootout) Penalties scored % What is the most and least successf...

Solution by Tom Prowse and you can download the workflow here .  This week we looked at recreating our own NBA Draft Lottery to help us predict what teams would get the top picks in the draft. The challenge turned out to be a tough one with some random functions not always playing ball and a lot of manual repeating work due to no looping functions. There are probably many ways of doing it, so if you have an alternative then please share it with us!  Step 1 - Join Seeds & Teams The first step is to combine the Seeding & Teams tables. These can be joined together by using the Seed field on both sides: After removing the extra Seed field our table will now look like this: Step 2 - First Round Pick We can now start to focus on each of the different rounds for which team has been selected. This is a similar process for each round so can be repeated for rounds 2, 3, & 4 as well.  The first step is to multiply the round number by 10 so that we remove the decimal and ...