I broke this challenge into 3 parts:
1.) Reshape Draft Pick Values
2.) Clean up NFL Draft Picks
3.) Combine & Calculate
Part I: Reshape Draft Pick ValuesThis is the part that really got me. I tried thinking of ways to use dynamic tools to do all reshaping in one step; like most things, I ended up having to balance time with practicality, so I ended up using a few tools to accomplish this.
-
Transpose - Get all of the data into 1 column
-
Formula - Now that all 14 column names are in 1 column, use a formula tool to standardize them (i.e, "Pick #" and "Value")
-
Multi-Row Formula - with the data being 100% vertical, use this tool to grab the pick number from the previous row
-
Filter - drop the rows that are no longer needed
-
Select - drop the unnecessary column
Part II: NFL Draft PicksUnion all 5 years of data, then change any Null grades to 0.
In the real world, I don't know that changing all Null grades to 0 makes sense. I noticed a few players with Null grades that were picked in one of the first few rounds of the draft, meaning they were probably highly-sought prospects, so
imputing the grade would have made more sense. However, there wasn't a lot of information to impute from and the instructions weren't clear on how to approach this. Without more information to go off of, assume "0" which ultimately matches the solution.
Part III: Combine & CalculateFrom there, the solution is pretty straightforward. Be sure to use a Union tool after the Join tool to keep all matches + any leftover data from Part II (i.e, drafts without a Draft Pick Value). Use a formula tool (or Data Cleansing tool) to change any Null Draft Pick Values to 2 per the instructions.
Sum up the grades and Draft Pick Values, divide, sort, sample, and browse!