2022: Week 26 - Solution

Solution by Tom Prowse and you can download the workflow here.

The challenge this week comes from Algirdas Grajauskas who has provided us with his Spotify data so we can tidy it up and then analyse it.

Step 1 - Minutes Played

Our first task is to create a new field that breaks down the milliseconds into minutes. To do this we can use the following calculation: 

Mins Played 

ROUND(

[ms_played]

/

(1000*60)

,2)

Multiplying by 60 allows us to convert into minutes, with a decimal based on the seconds. Note, after the decimal doesn't show how many seconds. This will need to be converted again if we need to calculate this.

After this our table should look like this: 

Step 2 - Extract Year

Next we need to extract the year from the timestamp field. We can use the inbuilt functionality within Tableau Prep for this. First we want to duplicate the timestamp field and rename it to Year, then press on the menu (three dots) icon to go to Convert Dates -> Year Number.

Step 3 - Rank Artists

Now we've extracted the Year, we now need to rank the artists based on the total minutes played overall, and then split this by year as well. 

Artist Rank

To calculate the overall rank for Artists, we first need to use an aggregation tool to calculate the total mins played for each one. In the aggregation, we need to group by Artist Name, then Sum Mins Played:

Then after the aggregation we can rank each of the artists using the Rank calculations:

Overall Rank

We no longer need the mins played so can remove that and our table should look like this: 

Year Rank

On a separate branch we now want to focus on the rank per year. This process is going to be similar as before, but this time we will group by Artist Name & Year, then Sum Mins Played in the aggregation tool: 

Then we can rank, but this time we want to make sure this is grouped by year:

Ranking

Again we can remove the Mins Played field and our 2nd table should look like this: 

Finally we can combine both of these tables together by inner joining on the Artist Name field:

At this stage our table should look like this:

Step 4 - Reshape Table

The final part of the challenge is to reshape the data so we can see how the artist position has changed over the years. To do this we want to pivot the years using a Rows to Columns pivot:

Then finally use a filter to limit the table to just the top 100 in the overall rank field: 

After this we are ready to output the data which should look like this: 

You can download the full output here. 

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!