Connecting to Online Services and APIs — Power BI Edition (With Snacks)

"If data is the new oil, APIs are the pipelines. And yes — sometimes pipelines leak."

---

You're already comfortable with relational databases and cloud data sources (nice work — remember that tutorial where we connected SQL Server and a blob store like a couple on a first date?). Now we level-up: online services and APIs. This is where Power BI talks to the internet, grabs JSON, negotiates OAuth, and occasionally falls into a rate-limit abyss. Welcome.

Why this matters

• Online services and APIs are how modern apps expose data: SaaS platforms (Salesforce, Stripe), cloud provider endpoints (Microsoft Graph, Google Analytics), developer APIs (GitHub), and custom REST services.
• Unlike relational tables, APIs return nested JSON, require auth flows, and use pagination and throttling. You need to transform, authenticate, and be gentle with rate limits.

---

Quick mental map: How this fits with what you learned before

• Relational DBs: predictable schemas, SQL queries, direct connections.
• Cloud sources: connectors for managed services (Azure SQL, S3, etc.).
• APIs: less predictable—you'll parse JSON, implement pagination, and handle OAuth or API keys.

Think: relational data is a tidy spreadsheet. API data is a messy inbox full of threads, attachments, and a few GIFs.

---

Authentication — the awkward handshake

APIs generally require one of these:

• API key (passed in header or query param). Simple, like a library card.
• Basic auth (username/password). Rare for production.
• OAuth 2.0 (Authorization Code or Client Credentials). The corporate handshake involving tokens, consent screens, and sometimes tokens with an attitude.
• Azure AD / Organizational account (Microsoft Graph and other Microsoft services). Smooth if your org uses Azure.

Power BI built-in "Online Services" connectors handle OAuth for many services. For everything else, use the Web connector and/or write M code with Web.Contents + custom headers/tokens.

---

Power Query basics for APIs (the magical glue)

Typical pattern:

1. Use Web.Contents to call an endpoint.
2. Parse the response with Json.Document.
3. Drill into nested records/arrays and convert to table.
4. Handle pagination to get all pages.

Example: simple GET with an API key in header

let
  url = "https://api.example.com/v1/transactions",
  headers = [Headers=[Authorization="Bearer YOUR_API_KEY", Accept="application/json"]],
  raw = Web.Contents(url, headers),
  json = Json.Document(raw),
  table = Table.FromList(json["items"], Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  table

Note: In Power BI Desktop, for sensitive values use parameters or the built-in credential UI, not hard-coded secrets.

---

Pagination: because APIs are lazy

APIs return pages. You must loop until there's no "next". Here’s a reusable pattern in M using a next-page URL in the JSON response:

let
  FetchPage = (url as text) =>
    let
      resp = Json.Document(Web.Contents(url)),
      items = resp[items],
      next = try resp[next] otherwise null
    in
      [Items=items, Next=next],

  FetchAll = List.Generate(
    () => FetchPage("https://api.example.com/v1/resources?page=1"),
    each _[Next] <> null,
    each FetchPage(_[Next]),
    each _[Items]
  ),

  allItems = List.Combine(FetchAll),
  table = Table.FromList(allItems, Splitter.SplitByNothing())
in
  table

If pagination uses headers (like Link header) or offset/limit, adapt the loop accordingly.

---

Real-world mini-examples (because theory without practice is just theater)

• GitHub repos: call https://api.github.com/orgs/ORG/repos, parse JSON, get name, created_at, stargazers_count.
• Microsoft Graph: use built-in connector or use /users and /groups via OAuth (Azure AD). Great for org reports.
• Stripe or Shopify: API keys passed in headers. Good for finance dashboards.

Question to chew on: If your company’s marketing dashboard needs ad performance from 3 ad platforms, each with different auth and pagination, how would you centralize refreshes? (Hint: look into gateway + parameterized queries or an intermediate Azure Function to normalize data.)

---

Error handling, rate limits, and being a polite API citizen

• Respect rate limits. APIs return HTTP 429 for too many requests. Implement backoff (wait + retry). In Power Query you can use a custom function with Function.InvokeAfter to wait between calls.
• Cache where sensible. If data updates hourly, don’t hammer the API on each preview. Use scheduled refresh cadence and cache in the gateway.
• Use the gateway for secured sources. Power BI Service needs gateway configuration for on-prem or certain authenticated flows.

Pro tip: If the API supports incremental or webhooks, use those. Incremental refresh is your friend for large datasets.

---

Practical tips & pitfalls

• Built-in connectors (Salesforce, Google Analytics, Dynamics, etc.) are easier — they handle auth & paging — but they may not expose every endpoint. When you need something custom, use the Web connector.
• When testing, use Postman or curl first to see JSON shape and headers.
• Avoid embedding secrets in M. Use parameters, query folding isn't possible with Web.Contents, so be mindful of transformation cost.
• Transform close to the source where possible. With APIs, that often means pulling only the fields you need (use query parameters to reduce payload size).

---

Quick checklist before you hit "Get Data"

1. Do you have the right auth method? (API key / OAuth / Organizational)
2. Can you test the endpoint in Postman? Know the JSON schema.
3. Do you need pagination? Plan a loop.
4. Is the refresh frequency compliant with rate limits? If not, slow down.
5. Will you use the service connector or Web.Contents with custom M?

---

Closing — TL;DR (but make it wise)

Connecting to online services and APIs in Power BI is where your analytics starts flirting with the messy, vibrant, ever-changing web of application data. It’s not the tidy relational world, but it's infinitely more powerful. Learn to handle auth, parse JSON, manage pagination, and treat APIs gently — and you’ll turn chaotic JSON into beautiful dashboards.

"APIs give you the data; your queries give it meaning. Treat both with respect."

Version name: "APIs but Make It Human"