Preppin' Data

Solution by Tom Prowse and you can download the workflow here . Step 1 - Split Nice or Naughty After inputting the data we can then start to split out the names on the Naughty or Nice list and the order in which they are on the list.  First we want to split the List and Order from the File Paths field. We can do this using an automatic split or using a split on the space. We can then rename split 1 to List and split 2 to List Order.  Step 2 - Index & Score Next we want to create an index field by combing the File Path number and id.  Index   ([List Order]*100)+[id] From here we can then calculate a Nice and Naughty Score:  Nice Score {FIXED [first_name]: SUM(IF [List] = 'Nice' THEN 1 ELSE 0 END)} Naughty Score   {FIXED [first_name]: SUM(IF [List] = 'Naughty' THEN 1 ELSE 0 END)} Then finally we can determine what list each name should be on based on their scores. Nice or Naughty?    IF [Nice Score] > [Naughty Score] THEN 'Nice' ELSEIF [Naugh...

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  [Res...