Round 1 Post | Round 2 Post | Round 3 Post |
Link to Test 3 Workbook - WITHOUT answers
Link to Test 3 Workbook - WITH answers
Finally, the round 3 post is here! Since my last post, we held the world finals round,
named our Canada Champ @Ghazanfar Abidi to be the #ExcelWorldChamp, and had the honor of hosting Ghazanfar come in Seattle/Redmond for the Microsoft Data Insights Summit and feedback sessions with the Excel Product Team. I hope Ghazanfar had a good time here, because the product team loved having him visit!
This post warrants the warning: my videos are still long. I get started talking about Excel and can’t stop! Because the video explanations are long, I’m going to try keeping this write up short. If the post doesn’t give you enough info, check the video. When the video is not good enough, hit the web and USE THE COMMUNITY! That’s why we are all here.
Challenge 01-01 (Data Relays part 1)
The purpose of this question was to get deeper into Get & Transform, which I LOVE. Get & Transform (aka PowerQuery) and the Data Model together is an insanely powerful combination. It dramatically changed the way I did my job when I was as a financial analyst. Building up skills on both tools can be a huge time saver when it comes to analytical work, and they are shared technologies between Excel and PowerBI
The other thing I will say (repeat of a comment in the video) is that there are many ways to get the answer. I’ve taken a path that intentionally uses lots of queries to show you the capability of modularizing your queries to use them as building blocks for one another (a great way to avoid repeating work and a way to make updates automatically flow through all your query work) Okay, onto to the nitty gritty.
Challenge 01-02 (Data Relays part 2)
TIME FOR THE DATA MODEL! The data model allows you to work with massive amounts of data (millions of rows), easily connect different data sets together via relationships, and run complex, dynamic calculations. Now, this is an introduction. We are just trying to get folks comfortable with the data model, before we unleash its awesome power! I HIGHLY recommend using some of the community resources/trainings for the data model. It really is a game changing technology for spreadsheet analytics. Again, the steps below are short and to the point. The video gives some deeper explanations of the data model for all the newbies out there. If you just want the answers, read below. If you want to get the details (and can bear listening to me) watch the video.
Challenge 02 (Chart Gymnastics)
Just like my prior posts, I won’t go into a lot of details here. There is no right answer. If you are new to chart building, go check out some of the references I provided in the round 1 post.
We selected this data set to see how people would handle separate but related data. The challenge asked comeptitors to
Showing all this at once is a tough thing to do. Naturally the community rose to the challenge!
Take a look at some of our most popular visualizations from this round….
@Diarmuid Early | @Daniel Salgado | @Vesa Tuomainen |
link to post | link to post | link to post |
Challenge 03 (Formula Wrestling)
Questions 1-3 are pretty straightforward because we thought questions 4 and 5 would be complex. So I won’t say a lot here in the write up. Check the video. Questions 1-3 are short and sweet answers.
Questions 4 and 5 have some extra comments in the file to help explain what’s going on. Again, use the video for questions 4 and 5. I mentioned in the video that the most formula “efficient” submissions for questions 4 and 5 used a brut force technique of calculating all possible routes in the map. This never crossed our minds as an option when we wrote the challenge. Well done outsmarting us! That said I wanted show some of the interesting ways you can leverage Excel formulas, so my answer implements Dijkstra's algorithm. For those of you not familiar Dijkstra’s algorithm, it is an approach to navigating nodes with connecting paths with of varying weights. The nodes and paths could represent traffic travel times, the speed of telecommunications transmissions, the costs of airline routes, etc… Check out this Wikipedia article on Dijkstra’s algorithm as a reference for the video.
Also, I start the video by saying I made it short and sweet. That ended up being a lie – sorry. I started talking, and you know how that goes…
There you have it, I know it’s a short write-up, but this is really a case where the video serves you much better than words. I loved trying to figure out how to implement Dijkstra’s algorithm. Hopefully you enjoyed the challenges and the solutions here.
More posts on the remaining tests will be coming (I swear) and thanks in advance for your patience.
As always, thanks for visiting the community!
Round 1 Post | Round 2 Post | Round 3 Post |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.