2025: Challenge 1 - Solution

Solution by: Rosh Khan 

You can download the workbook from here

Step 1- Input Data 

As with all challenges, the data can be downloaded from the challenge requirements. As we are working with Excel files, we can choose Microsoft Excel as our connection type.

Step 2 - Split the field ‘Name’ into Game Title and Developer

We know that the field ‘Name’ contains two pieces of information: Game Title and Developer, and therefore needs to be separated. The characters separating the two in this case are “ (made by “, therefore, we can click on More Options under the field ‘Name’ -> Split Value -> Custom Split -> and define our separator to be “ (made by “  and Split off the first 2 fields.

Please note: There is a space before ‘)‘ and after ‘by’

After applying the following formatting:

• Change field names to be ‘Game Title’ and ‘Developer’

• Removing trailing and leading spaces from both fields

• Removing punctuation from the ‘Developer’ field (More Options -> Clean -> Remove Punctuation) 

• Deleting the original column

Our cleaned fields look like this:

Step 3 - Extract 5 star Ratings 

From the field ‘User_Review’ we only want to keep the reviews that are 5 star rated. This is a simple fix which includes keeping only the 1 first character of the values. Using a calculated field will do that for us:

left([User_Review],1)

As we only care about the 5 star ratings, and the ones that don’t have a rating out of 5 can be excluded from our analysis, we can right click the value 5 and choose ‘Keep Only’

Step 4 - Convert Place_Sold to Currency

We need to start thinking about joining our Sales data to the GBP Exchange Rates data. The only issue is that our joining fields (‘Place_Sold’ from Sales and ‘Currency’ from GBP Exchange Rates) have different values.

Since there are only 4 places the company is selling in, we can utilise a logical statement to convert one of our fields to match the other. As we have been working on the Sales data, let’s do it here. Creating a calculated field on the ‘Place_Sold’ field with the following statement will bring us the results we want to be able to join: 

if [Place_Sold] = 'Europe' then 'EUR'

elseif [Place_Sold] = 'Japan' then 'JPY' 

elseif [Place_Sold] = 'United Kingdom' then 'GBP'

elseif [Place_Sold] = 'USA' then 'USD'

end

We can name this field ‘Currency’ to help Tableau Prep automatically pick up the joining field. 

Step 6 - Join the two datasets

Now join the Sales data with the Exchange Rates data by defining our Join Clauses as both date and Currency.

Doing this will create duplicate fields which we need to delete: Date-1 and Currency-1 

Step 7 - Calculate Sales in GBP from different currencies

This requires a simple multiplication of the ‘Price_Sold’ field with the ‘Exchange Rate’ field. However, before that, we need to convert ‘Price_Sold’ from a String to a Number (Decimal).

Then create a calculated field called ‘Sales (GBP)’ using this: 

[Price_Sold] * [Exchange Rate]

Step 8 - Aggregate data to find top selling games

We can now remove all the extra fields and on top find the total sum of sales made by each game by adding an aggregate step where the grouped field is ‘Game Title’ and the aggregated field is ‘Sales (GBP)’.

Step 9 - Rank the games

Now we need to Rank all the games in terms of their total sales so we can only keep the the top 5 games. To do so, We can start creating a rank calculation using this: 

The Rank settings are as such:

Finally we need to only keep the first 5 based on the rank. We can select them whilst holding down Ctrl (or Cmd) on your keyboard and selecting Keep Only.