PowerBI Data Connections with OpenApply Public API

Power BI provides several ways to retrieve and work with data from OpenApply API. In this article, we explore three common methods:

1. Direct Web Connection

2. M-Code in Power Query

3. Python Scripts

When connecting to the OpenApply API, you should use query parameters to limit the amount of data fetched and improve performance. Using these parameters helps ensure faster responses and reduces unnecessary data transfer during each API call. In the examples below, we demonstrate using since_date, enrollment_year, and status parameters to fetch a targeted subset of student records. If entire data sets is required, we recommend to schedule such requests once per day during the school’s nighttime hours

Prerequisites: Authentication

Before you can use M-Code or Power BI’s web connection, OpenApply API requires an authentication token to access endpoints. You’ll need to request this token through the /oauth/token endpoint. This token is generated using your Client ID and Client Secret, which you can obtain from the OpenApply Integrations panel. The following article provides detailed guidance on how to generate them.

One simple way to generate the auth token is by using a short PowerShell script. Replace Client ID and Client Secret values with your actual OpenApply API secrets:

$ClientID = "xxx"
$ClientSecret = "yyy"

# Combine Client ID and Client Secret, then Base64 encode them
$AuthHeader = [System.Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("${ClientID}:${ClientSecret}"))

# Make the request to get the access token
$Response = Invoke-RestMethod -Uri "https://api.openapply.com/oauth/token" `
-Method Post `
-Headers @{Authorization = "Basic $AuthHeader"} `
-Body "grant_type=client_credentials" `
-ContentType "application/x-www-form-urlencoded"

# Display the access token in the console
Write-Output "Access Token: $($Response.access_token)"

# Optionally, save the access token to a text file
$Response.access_token | Out-File -FilePath "access_token.txt" -Encoding utf8

This script saves the access token and displays it in the console. Now that we have a valid token, we can move on to extracting data from the OpenApply API using Power BI.

Another option to obtain the authentication token is by using a cURL command in your command line. This method sends a POST request to the/oauth/token endpoint and returns a ready-to-use token for your API calls:

curl -X POST "https://api.openapply.com/oauth/token" \
-H "Content-Type: application/x-www-form-urlencoded" \
-H "Authorization: Basic YOUR_BASE64_ENCODED_CREDENTIALS" \
--data-urlencode "grant_type=client_credentials" \
--data-urlencode "client_id=YOUR_CLIENT_ID" \
--data-urlencode "client_secret=YOUR_CLIENT_SECRET"

Direct Web Connection

Choose New Source -> Web type of connection:

This connection uses the Advanced Web connector in Power BI to pull data directly from the OpenApply API. In this example we are calling the endpoint https://api.openapply.com/api/v3/students with filters for page=1, status=pending, and enrollment_year=2025. The Authorization header uses a Bearer token obtained from previous step to securely authenticate the request.

Later on, you can also use Power BI’s “Manage Parameters” tool to handle the token as a parameter, making it easier to update when it expires; however, this approach is not covered in this guide.

Once you have successfully connected to the OpenApply API using the Web connector in Power BI, the tool performs the API call and retrieves the data. At this stage, the data is usually displayed in a nested or hierarchical format, especially when working with JSON responses.

The next step is to transform this raw response into a structured table that you can work with in Power BI.

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, and click OK

After loading the data into Power Query, you may notice that some columns contain nested tables or lists. This is common when working with JSON data, where related records are grouped inside other tables.

To view and work with this information, click the Expand icon next to the column name and choose “Expand to New Rows”.

Use this step carefully. Expanding columns can significantly increase the number of rows in your dataset, which may impact performance and make transformations more complex

M-code in Power Query

M-Code language behind Power Query allows you to create more flexible and dynamic queries. This method is ideal when you need custom headers, authentication tokens, or looping through multiple pages of an API. Choose New Source -> Blank Query type of connection:

Then, right-click on the query, choose Advanced Editor and paste the code written below:

This M-code fetches student records from the OpenApply API using a since_date parameter to limit results to records updated after a specific date. The parameter can be adjusted manually or defined dynamically as a Power BI query parameter. It returns a ready-to-use Power Query table in a single step and handles pagination automatically, so you don’t need to manually combine pages or expand lists:

Always limit your requests using parameters such as since_date. Running large, unrestricted queries may cause your API requests to be logged or throttled by the server. The OpenApply API is designed for data synchronization to fetch data periodically and edit it locally, not for maintaining a live connection that repeatedly pulls full datasets.

let
    AccessToken = "<replace YOUR_ACCESS_TOKEN with the actual token>",
    BaseUrl = "https://api.openapply.com/api/v3/students",
    since_date = "2025-01-01", // <-- always limit your records

    // Function to get data from a specific page
    GetPage = (PageNumber as number) =>
    let
        Url = BaseUrl & "?since_date=" & since_date & "&page=" & Number.ToText(PageNumber) & "&per_page=50",
        Headers = [
            #"Authorization" = "Bearer " & AccessToken,
            #"Content-Type" = "application/json"
        ],
        Response = Web.Contents(Url, [Headers = Headers]),
        JsonResponse = Json.Document(Response),
        Data = try JsonResponse[students] otherwise {}
    in
        Data,

    // Recursive function to fetch all pages
    FetchAllPages = (PageNumber as number, AccumulatedData as list) =>
    let
        CurrentPageData = GetPage(PageNumber),
        NewAccumulatedData = List.Combine({AccumulatedData, CurrentPageData}),
        NextPageNumber = PageNumber + 1
    in
        if List.IsEmpty(CurrentPageData) then
            AccumulatedData
        else
            @FetchAllPages(NextPageNumber, NewAccumulatedData),

    // Combine all pages into one table
    AllData = Table.FromRecords(FetchAllPages(1, {}))
in
    AllData

You can also export data from other API endpoints by creating separate queries in Power Query. For example, you can duplicate the query structure shown above and adjust the BaseUrl for each resource providing more filtered results.

This approach allows you to manage each dataset independently, apply different transformations, and then combine them later in Power BI if needed.

Python Scripts

For advanced scenarios, you can use Python to retrieve data and export it to a JSON or CSV file, which Power BI can then import. This is useful when working with APIs that require more complex handling or when integrating with existing Python workflows. Learn more about Python prerequisites: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-python-scripts

The script sends a GET request to the specified API endpoint using your access token for authentication. It retrieves the response data in JSON format.

Go to Get Data → More → Python script.

import requests
import pandas as pd

# Note:
# Always include the parameter such as "since_date" to limit results.
# Avoid running full, unrestricted API calls — large data pulls can be logged or throttled by the server.
# The OpenApply API is intended for scheduled syncs, not continuous full data downloads.

url = "https://api.openapply.com/api/v3/students"
headers = {
    "Authorization": "Bearer YOUR_ACCESS_TOKEN",
    "Content-Type": "application/json"
}

all_students = []
page = 1
per_page = 5
since_date = "2025-01-01"  # <-- Added parameter to limit results

while True:
    response = requests.get(url, headers=headers, params={
        "page": page,
        "per_page": per_page,
        "since_date": since_date  # <-- Added to request parameters
    })
    data = response.json()
    students = data.get('students', [])
    
    if not students:
        break
    
    all_students.extend(students)
    page += 1

# Convert to pandas DataFrame
df = pd.json_normalize(all_students)

# Output for Power BI
df