I came across this football-data.org database and decided to create a Power BI report with the latest scores and standings of the top European football leagues (competitions). The challenge was that the data is accessed via API token and can be queried for one competition and one season at a time. In another words, you cannot import a full dataset and slice and dice freely.
How to set up Parameters
I decided to use PARAMETERS to easily switch my requests between competitions and seasons.
Step1: Using web connector I connected to the football-data.org database and hardcoded the English Premier League (PL) and current season (2020) to get the match results
Step 2. I created two parameters (Home – Transform Data – Manage Parameters – New Parameter) listing available Competitions and Seasons
Step 3. Going back to the ‘Source’ step, I replaced the hardcoded Competition and Season with the newly created parameters. Using the Advanced option of the web connector, I split the URL into parts:
The URL contains now the parameters instead of hardcoded elements. Here’s the adjusted URL:
http://api.football-data.org/v2/competitions/{Competition}/matches/?season={Season}
Step 4. In order to get ‘Standings’ and ‘Top Scorers’ in addition to ‘Match Results’, I added two new queries by slightly modifying the existing URL:
https://api.football-data.org/v2/competitions/{Competition}/standings/?season={Season}
https://api.football-data.org/v2/competitions/{Competition}/scorers/?season={Season}
Step 5. I transformed the queries using Power Query, modelled and visualized the data.
Step 6. I saved the report as TEMPLATE (.pbit)
How to Consume data using TEMPLATE
Now, whenever somebody opens the template they needs to enter their personal API key (or use mine) and select Competition and Season:
The report opens promptly and it contains the most recent Match Results, Standings and Top Scorers
How to switch parameters
Switching parameters to another league or season is simple, though not intuitive. You need to go to Home – Transform Data – Edit Parameters, and apply changes:
Because you import a relatively small amount of data at a time (one league, one season), the report is light and connects / refreshes quickly.
Summary
Using PARAMETERS + TEMPLATE can be an efficient alternative to the traditional way of importing full datasets and consuming heavy reports. Users import and consume selected information (region, product, year, etc) benefitting from clarity and improved performance of the report.
Here the link to the Power BI template on One Drive