Connecting Power BI

Every resource is plain JSON over HTTPS, so Power BI reads it through the Web connector. This guide covers the token header, paging past the row cap, and a model shape that works.

1. One query, one resource

In Power BI Desktop: Get Data → Web → Advanced, then set the URL and add an Authorization header.

Use the Advanced editor so the token lives in a parameter rather than being pasted into every query:

let
    Token    = "Bearer " & TokenParam,
    Source   = Json.Document(
        Web.Contents(
            "https://api.partners.thecareeros.com/v1",
            [
                RelativePath = "advisor_students",
                Query        = [ limit = "1000", offset = "0" ],
                Headers      = [ Authorization = Token ]
            ]
        )
    ),
    Table    = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1",
        { "id", "first_name", "last_name", "email", "cohort_name", "status",
          "profile_completeness", "assigned_advisor_name" })
in
    Expanded

Create TokenParam as a text parameter (Home → Manage Parameters). Keeping the base URL in the first argument of Web.Contents and the rest in RelativePath/Query is what lets scheduled refresh work in the Power BI Service — a fully dynamic URL string will be rejected as a dynamic data source.

2. Page past 1 000 rows

Responses are capped at 1 000 rows. Anything larger has to be paged. This function fetches one page:

// fnGetPage
(resource as text, offset as number) as list =>
    Json.Document(
        Web.Contents(
            "https://api.partners.thecareeros.com/v1",
            [
                RelativePath = resource,
                Query        = [ limit = "1000", offset = Number.ToText(offset) ],
                Headers      = [ Authorization = "Bearer " & TokenParam ]
            ]
        )
    )

And this loops until a page comes back short, which is the signal that you’ve reached the end:

let
    Pages = List.Generate(
        () => [ Offset = 0, Data = fnGetPage("student_engagement_monthly", 0) ],
        each List.Count([Data]) > 0,
        each [ Offset = [Offset] + 1000,
               Data   = fnGetPage("student_engagement_monthly", [Offset] + 1000) ],
        each [Data]
    ),
    All   = List.Combine(Pages),
    Table = Table.FromList(All, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

Rate limit is 600 requests/min, so a full extract of a few hundred thousand rows stays well inside it. There’s no need to throttle beyond Power BI’s natural pace.

3. Fetch less

Two habits make refreshes much faster:

Project only the columns you use — select trims the payload server-side:

Query = [ select = "student_id,cohort_name,month,application_submitted", limit = "1000" ]

Filter server-side, not in Power Query. Push the date range into the request rather than loading everything and filtering after:

Query = [ month = "gte.2026-01", limit = "1000" ]

/student_engagement and /student_engagement_monthly are the two expensive resources — they scan the full activity history for your universities. Schedule those on a nightly or weekly refresh rather than hitting them interactively.

4. A model that works

Load these as separate queries and relate them on the UUID columns:

Relate student_engagement_monthly[student_id] → advisor_students[id] (many-to-one), and add your own date table joined on month_start so time intelligence works.

5. Measures that avoid the common traps

Attendance rate must ignore events where attendance was never recorded, or untracked events silently deflate it:

Attendance Rate =
DIVIDE(
    CALCULATE( SUM( events[attended] ),    events[attendance_tracked] = TRUE() ),
    CALCULATE( SUM( events[registrations] ), events[attendance_tracked] = TRUE() )
)

Active-student rate needs student_engagement rather than the monthly table as its denominator, because the monthly table has no rows for students who never acted:

Active Student Rate =
DIVIDE(
    COUNTROWS( FILTER( student_engagement, student_engagement[active_months] > 0 ) ),
    COUNTROWS( student_engagement )
)

Don’t cross-add company_saved and /companies. The engagement metric counts every save including the CareerOS company auto-assigned to each student; /companies excludes those seeded rows. Both are correct for their own question, but they will not reconcile.

6. Scheduled refresh in the Power BI Service

After publishing, open Dataset settings → Data source credentials and set the web source’s authentication to Anonymous — the token travels in your Headers record, not in Power BI’s credential store. Then set TokenParam under Parameters, so rotating the token is a settings change rather than a republish.

Tokens are typically valid for a year. When one is rotated, update the parameter in the Service and in any local .pbix files.

Troubleshooting