The Tokyo 2020 Olympics opening ceremony is only days away, and some of the qualifying matches are already underway. I say 'already'; I guess they're only a year late!
This is the first blog in a short series designed to:
- demonstrate common Power BI principles using a non-sales related dataset
- address common pain points when working with non-fact table measures
- keep up to date with the latest stats in the Tokyo 2020 Olympic games
- cheer for the little guy - we're going to throw population into the mix and see how the big players stack up now
Over the coming weeks I'll document how I created the reports using Olympic medals data readily available on the web.
Power Query Parameters
I've blogged about parameters in Power Query before. The 'Get Data from Folder' packages up data for us nicely and does all the custom function work, but what if you have something tricky or different you need to handle in each file? What if all the sheets in Excel are named the same as the filename - the Get Data from Folder won't allow you to pick a different sheet name each time. Parameters can make this possible, but we need to get familiar with using parameters and be able to understand how the data fits together.
This is an easy demonstration on using parameters, and it uses data that's readily available on the web. As a bonus, you can use this technique to crawl a website to get the lowest prices or read all the reviews on a product you're looking to buy. Parameters are all about finding the patterns in your data source, which in our case today is the URL of the website.
In this sample, we connect to Olympics.com using two main URLs:
- Parameter Table: https://olympics.com/en/olympic-games/olympic-results
- Sample URL: https://olympics.com/en/olympic-games/pyeongchang-2018/medals
The https://olympics.com/en/olympic-games/olympic-results site gives a simple table of all the Olympic games throughout history, and thankfully they are formatted in a way that is very similar to the Sample URL for each medals table.
When pulled into Power Query, the above table looks like this:
In the first half of the video below, I walk through how I turned this table into a complete list of all Teams who have won medals for each of the Olympic games.
