Every 4 years I have a ritual.
Of course, this data is super messy, so the first thing I did was to bring it into “Get & Transform” using the “From Table/Range” button in the “Data Tab”. Note the ribbon might look a little different depending on which version of Excel you have.
I cleaned the data by applying several transformations in the Query Editor, including removing columns and errors, splitting columns, joining tables, adding conditional columns, etc.. The result was a clean table with just the scores for each game (see below).
The fun part was building the logic for Points, Goals and tie breakers exclusively using Get & Transform. BTW - I know there are multiple ways of building this in Excel, and would love to see other approaches. The next challenge for myself will be to build the logic using PowerPivot and DAX.
At a high level, the logic that I built is as follows:
Finally, I combined the historical results table with the score predictions table and brought the standings table back to the grid in Excel.
In the process of building this model, I also learned about the “Query Dependencies” view that quickly helped me visualize how all the steps and queries are interconnected (see below).
To access this view, while in the “Query Editor” click on the “Query Dependencies” button in the “View” Tab.
As promised, here is a link to my South American Qualifying spreadsheet for you to build your own predictions. Note that you might get prompted by a "Security Warning" in Excel, click "Enable Content" to proceed. If you want to see the details behind each of the queries, go to the “Data Tab> Get Data> Launch Query Editor”. If you want to predict your own scenarios and outcomes you can simply change the scores in the “Score Predictions” table, and click “Refresh All” in the Data Tab. This will update the standings table.
The next challenge will be to build the logic using Power Pivot and DAX.
- Excel Team
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.