Please find below steps necessary on how to use OpenApply's APIs to PowerBI.
This example uses an OpenApply API and so some of the steps will be different for different APIs. It also uses the students endpoints, but can be applied to any other endpoint as well.
First, set up a bearer token for authentication on the OpenApply v1 API. The v1 API is exactly the same as the v3 API apart from the way you authenticate. If you are using ManageBac's APIs, this token is the same token generated in the API manager.
In PowerBI Desktop
- Click on Get Data
- Click on Web
In the From Web box, paste the API URL in the format
https://{subdomain}.openapply.{tld}/api/v1/students?auth_token={token}
Where {subdomain} is your school's subdomain, and {tdl} it your top-level domain ("cn" in China region, "com" elsewhere). The {token} should be replaced with your bearer token.
Tip
Please note that by default 100 results will be downloaded. To view more, you can extend the amount of results per page, or change the page number in the API URL. For example:
https://f1.openapply.com/api/v1/students?per_page=200&page=2&auth_token=1a2b3c...
Note
If you are using ManageBac's APIs, note that it does not require {subdomain}, use for example https://api.managebac.com/v2/students instead.
You will be presented with the following screen in Power Query Editor:
We will first process the students so change the name of the query to students for future reference
Click on List for students:
Click To Table
Click OK in the pop up box
Click the Expand Column icon for Column1
Uncheck the Use original column name as prefix
Click the Expand Column icon for tags
Click Expand to New Rows
Click the Expand Column icon for sibling_ids
Click Expand to New Rows
Click the Expand Column icon for parent_ids
Click Expand to New Rows
Click Close & Apply
This will load the fields available for students to be added to visuals within Power BI
