For this third part of Power BI Web sources, we will explore the use of Partner API in Power BI. This can be done in multiple ways; however, you must add code to your solution. Since, this relies on code no matter what I want to take this opportunity to combine Power BI with python query. Doing this demands that you have a python client installed on the computer and that you have installed the libraries Requests and Pandas.
We will look at a very simple partner API, because this demands very little manipulation of the data. We are going to use the football prediction API (https://rapidapi.com/boggio-analytics/api/football-prediction/). This is one of many APIs offered by rapid API, it demands that you have an account on RapidAPI. Next you need to subscribe to this specific API. It has a free connection option.
On this page you can get code examples for most coding languages, for this we are going to look at python. You can find examples in the documentation; we will however do one here and explain the different segments.
First, we will import the needed libraries:
We import requests to allow us to interact with web-based sources, and pandas allows us to convert the retrieved data to a table.
Next, we define a function to generate the header, this is where we add our token and any other connection related parameters. Usually, the API documentation will specify what you need in your header.
Next, we define a function generating the query string, this is also seen as the parameters for the API call. This function has 3 variables, market, date, and federation, possible values can be found in the API documentation.
Next, we will retrieve the API token, I have saved it in a separate text file to increase security.
Now we are ready to generate the variables for our API call, we do this by assigning the URL, headers, and parameters.
We call the API with a requests.get call and using the URL, headers, and parameters. You can test if everything is working after this step by using a print(response) this must return 200.
Finally, we will convert the response information to a table using pandas dataframe. Here we first convert the response to JSON, and then what the value for the key “data”. This key varies from API to API. Now we will convert the dictionary we have in result_ to a dataframe (table).
Now we are ready to get the information into Power BI, to do this go to get data -> more -> Other -> Python script.
Here we copy paste our script:
Now a window will appear where you can choose the table to import, this means you can have many data frames in one script and import the ones you want to use.
I hope this dive into how to use APIs in Power BI have help some of you out there wanting to expand on the possibilities within Power BI. If you have any question, please reach out and contact us.