2024: Week 51 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Clean Week
First we can clean the table so that we remove any rows within the Couple field that are equal to 'Couple'. These are the headers from where the web scraping wasn't quite accurate.
We can then focus on the Week field and making sure that it's a numeric field. First we can split the field so that we only have the information from before the first ':'
We can then use this field to remove any punctuation and letters so that we only have a number remaining. We can then remove the other fields, rename this one to Week, and make sure it's a whole number.
Step 2 - Finalists
Next we want to filter so that we're only looking at the finalists from each series. First we want to make sure that the 'Runners-Up' values are grouped together and have the same spelling.
Then we want to create a field to identify the finalists:
Finalist
IF
[Result]='Winners' OR
[Result]='Runners-up' OR
[Result]='Third place'
THEN [Result]
END
We can then use an LOD to bring the position onto each row:
Finalist Position
We can then remove any Null values from the Finalist Position field and remove the Finalist field so the table looks like this:
Step 3 - Scores & Judges
Next we can focus on the scores that each couple got each week. First we want to split the total score from each of the individual judges score by splitting on the '(':
We can then remove the original score and rename the split-1 to Score then make it a whole number.
For the Split-2 field, we can use this to count how many judges were involved in the scoring. For this we want to make sure all the numbers and spaces have been removed then we can use a Len function to count the remaining punctuation which will also equal the number of judges.
Number of Judges
LEN([Scores - Split 2])
Then from here we can use this to find the Avg score:
Avg Judge's Score
[Score]
/
[Number of Judges]
Finally we can aggregate the scores so that we have a total per couple and week:
Step 4 - % Change
The last step is to analyse how the scores have changed. First, we want to identify the first and last week for each couple:
First Week
Last Week
We can then filter to contain weeks that are only the first or last using a calculation:
[Last Week]=[Week]
OR
[First Week]=[Week]
Then finally we can calculate the % change:
% Change
The last part is to remove any null values from the % Change and then remove the Week field. Our final table is now ready to output:
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 & @TomProwse1
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
