Have you ever wondered what our Power Platform experts do in their spare time? Aside from the normal family oriented fun activities others enjoy, we occasionally challenge each other with a data question and often, it is a doozy.
This recently happened with a question from our Founder and CEO Rob Collie. He presented a question in our Slack channel regarding which movie had the most decorated cast based on having received an Academy Award for acting. While many people chimed in with an answer of their favorite movie or which movie cast they thought would have the most awards, one member of our team took the actual question to heart and devised a method to answer the question with facts. . . and to hone his Power Platform skills at the same time.
According to Dave Rivera, P3 Adaptive Principal Consultant, he felt that the answers presented in the channel lacked factual basis. While the question seemed to have simple criteria, the suggested answers had no research to back up the claims. In Dave’s words, “Rob challenged us” with a topic that would be a great project with Power BI. Still being new with P3, and a movie buff to boot, Dave accepted challenge and began the process to answer the seemingly simple question.
All in all, two weeks later, Dave was finally ready to share his answer. It didn’t take two weeks of working straight through, it was two weeks from when the question was posed to when the answer would be ready. From start to finish with an hour snuck in here or there in his free time, Dave built an elegant, accurate solution that was completed in under eight hours. And, truth be told, most of that time was utilized to create a plan of attack for scraping the necessary information from the web and deciding on the logic for ranking movies, not on the actual building process.
And, today, we are going to share his solution.
Dave’s plan of attack included scraping Wikipedia for a table of all Academy Award nominees recorded since 1933. Power Query was more than up for the task on that step. The next step, however, required Dave to obtain a historical list of all actors and the filmography attributed to each of them. For this data, Dave turned to IMDB. In total, IMDB’s list included 954 actors, with dozens of movies credited to each, all listed on separate pages on the site. And all those pages would need to be scraped to compile the data in a spreadsheet for use in Power BI.
Thankfully, for this step, Dave was able to use Power Automate to create a flow, set it, and walk away.
Once the data was collected, logic was created to rank the actors by nominations and wins. At this point in the process, there was a clear leader in the nominations and awards, Meryl Streep, making it likely that a Meryl Streep movie would be in the top contenders. But Dave didn’t start this process to create an assumption, so he carried on in a thoughtful manner to determine the actual winner by creating a ranking system for the movies based upon the cast involved. Once that was completed, it was a simple process of displaying the data in a user-friendly format.
You may view and interact with the full dashboard below!
Or click here to open it in a new window.
While this sounds like an exceedingly complex process, the model behind the Power BI report is strikingly simple and consists of two Lookup Tables, a Fact Table, and a Key Measures Table. Simple, clean, and exceptionally efficient.
All in all, this project may have seemed overwhelming during the initial brainstorming phase, but as Dave has so expertly shown, not all complex questions are complicated to solve. Putting the Power Platform into action to build the solution meant that time, energy, and resources were appropriately allocated. This same creative process can be applied across any data question whether for a hobby, an educational use, or even to answer a pressing business question.
As you can see, our experts truly enjoy problem solving and building solutions. If you or your company has a complex question you need answered or if you are interested in learning the necessary skills and methodology for yourself, reach out to us. We will be happy to provide the expert guidance or instruction you need.
Your email address will not be published.