Connecting Power BI to REST APIs and Web Data Sources: Authentication, Pagination, and Automated Data Refresh

Introduction

Your data doesn't live in a database. At least, not all of it. A growing share of the most valuable business data in the world — CRM records, marketing metrics, financial feeds, weather data, logistics tracking — lives behind REST APIs. If you're still manually exporting CSVs from your SaaS platforms and dumping them into Power BI, you're doing it the hard way, and you're leaving a lot of automation on the table.

Power BI's Web connector and Power Query's Web.Contents function give you a direct pipeline into virtually any REST API. But the gap between "I got it to work once" and "this refreshes reliably every night without breaking" is wider than most tutorials acknowledge. Authentication schemes, paginated endpoints, rate limits, and the quirks of Power BI's scheduled refresh engine all have the potential to turn a working prototype into a fragile, maintenance-heavy mess. This lesson bridges that gap.

By the end of this lesson, you'll have built a production-grade Power BI data source that connects to a real paginated REST API, handles authentication properly, retrieves all pages of data, and refreshes automatically on a schedule — without anyone manually clicking anything.

What you'll learn:

• How to use the Power BI Web connector and Web.Contents function to call REST API endpoints
• How to implement common authentication patterns: API keys, Bearer tokens, and Basic auth
• How to handle paginated APIs using both offset-based and cursor-based pagination strategies
• How to configure Power BI Gateway and scheduled refresh so your data stays current
• How to avoid the most common mistakes that cause refresh failures in production

---

Prerequisites

You should be comfortable with Power Query's M language at a basic level — you know what let...in blocks are, you've written at least a few custom transformations, and you're not intimidated by seeing a function call. You should also have access to Power BI Desktop and a Power BI Pro or Premium Per User license (required for scheduled refresh in the service). If you want to follow the hands-on exercise exactly, you'll need a free API key from Open-Meteo (no auth required) and optionally a free account at The Movie Database (TMDB), which uses API key authentication.

---

Understanding How Power BI Talks to REST APIs

Before you write a single line of M, it helps to understand what Power BI is actually doing when it connects to a web source. Every REST API call is fundamentally an HTTP GET (or occasionally POST) request: your client sends a request to a URL with optional headers and parameters, and the server responds with a payload — usually JSON.

Power Query's Web.Contents function is your interface to this process. At its simplest, it looks like this:

Web.Contents("https://api.example.com/data")

That returns a binary response. You almost always pipe it through Json.Document to parse it:

Json.Document(Web.Contents("https://api.example.com/data"))

The result is a Power Query record or list that you can then navigate and expand into a table.

But Web.Contents has more power than that simple signature implies. Its full signature is:

Web.Contents(url as text, optional options as record) as binary

That options record is where everything interesting happens. It accepts fields including:

• Headers — a record of HTTP headers to include (this is how you do authentication)
• Query — a record of URL query parameters (cleaner than building query strings by hand)
• Content — binary content for POST requests
• RelativePath — a path segment appended to the base URL (important for scheduled refresh, as we'll discuss)
• ManualStatusHandling — a list of HTTP status codes you want to handle yourself instead of letting Power Query throw an error

Understanding the RelativePath option in particular is critical and often misunderstood. When Power BI's scheduled refresh engine validates a data source, it checks the base URL of any Web.Contents call. If you build dynamic URLs by concatenating strings into the first argument, the refresh engine sees a different URL each time and may fail to apply the correct credentials or may throw a Formula.Firewall error. Using RelativePath and Query to pass dynamic parts keeps the base URL stable.

Here's the pattern you should internalize:

// DO THIS: base URL is stable, dynamic parts go in options
Web.Contents(
    "https://api.example.com",
    [
        RelativePath = "/v1/records",
        Query = [page = "1", per_page = "100"]
    ]
)

// DON'T DO THIS: dynamic URL breaks scheduled refresh
Web.Contents("https://api.example.com/v1/records?page=1&per_page=100")

---

Handling Authentication

Authentication is the first place most REST API connections fall apart. Power BI has a built-in credential system that works great for OAuth2 against common providers (SharePoint, Dynamics, etc.), but for the wide world of custom APIs, you'll often be managing auth yourself in M code.

API Key in a Query Parameter

Many APIs — especially public data APIs, mapping services, and developer-tier SaaS tools — authenticate with a simple API key passed as a URL parameter. The TMDB movie database is a perfect example:

let
    ApiKey = "your_api_key_here",
    
    BaseUrl = "https://api.themoviedb.org",
    
    Response = Web.Contents(
        BaseUrl,
        [
            RelativePath = "/3/movie/popular",
            Query = [
                api_key = ApiKey,
                language = "en-US",
                page = "1"
            ]
        ]
    ),
    
    JsonResponse = Json.Document(Response),
    Results = JsonResponse[results],
    ResultTable = Table.FromList(Results, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(
        ResultTable, 
        "Column1", 
        {"id", "title", "release_date", "vote_average", "popularity"}
    )
in
    Expanded

Important: Don't hardcode API keys directly in your M code if you're sharing the PBIX file. Instead, use a Power Query parameter to hold the key value. This lets you change it in one place and makes it less likely you'll accidentally commit credentials to a shared location.

API Key in a Header

A more secure (and increasingly common) pattern is passing the API key as an HTTP header, often named X-API-Key or Authorization. Here's how that looks:

let
    ApiKey = "your_api_key_here",
    
    Response = Web.Contents(
        "https://api.example.com",
        [
            RelativePath = "/v2/campaigns",
            Headers = [
                #"X-API-Key" = ApiKey,
                #"Accept" = "application/json"
            ]
        ]
    ),
    
    JsonResponse = Json.Document(Response)
in
    JsonResponse

Note the #"..." syntax for header names that contain hyphens. Power Query record keys that include special characters need to be quoted this way.

Bearer Token (OAuth2-Style)

Many enterprise APIs use Bearer tokens — a string you obtain through a separate authentication step and then pass in the Authorization header for subsequent requests. If your token is static (a personal access token or long-lived service token), the pattern is straightforward:

let
    Token = "eyJhbGciOiJSUzI1NiIsInR5cCI6...",  // your token
    
    Response = Web.Contents(
        "https://api.example.com",
        [
            RelativePath = "/v1/reports",
            Headers = [
                Authorization = "Bearer " & Token,
                #"Content-Type" = "application/json"
            ]
        ]
    ),
    
    JsonResponse = Json.Document(Response)
in
    JsonResponse

If your token expires and needs to be refreshed dynamically (true OAuth2 flow), the pattern is more complex. You first make a token request, extract the token from the response, then use it in subsequent calls:

let
    // Step 1: Get access token
    TokenResponse = Json.Document(
        Web.Contents(
            "https://auth.example.com",
            [
                RelativePath = "/oauth/token",
                Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
                Content = Text.ToBinary(
                    "grant_type=client_credentials" &
                    "&client_id=your_client_id" &
                    "&client_secret=your_client_secret"
                )
            ]
        )
    ),
    
    AccessToken = TokenResponse[access_token],
    
    // Step 2: Use token to fetch data
    DataResponse = Json.Document(
        Web.Contents(
            "https://api.example.com",
            [
                RelativePath = "/v1/data",
                Headers = [Authorization = "Bearer " & AccessToken]
            ]
        )
    )
in
    DataResponse

Warning: Dynamic token flows like this can cause Formula.Firewall errors in Power BI service because Power Query evaluates queries in a privacy-partitioned sandbox. If you hit this, try setting the privacy level of your data sources to "Organizational" in the Power Query options, or consider whether a dataflow (which has fewer firewall restrictions) is a better approach for this use case.

Basic Authentication

Basic auth encodes a username and password as a Base64 string in the Authorization header. Power Query doesn't have a built-in Base64 encoder for text, but you can accomplish this with the Binary.ToText function:

let
    Username = "api_user",
    Password = "secret_password",
    
    Credentials = Username & ":" & Password,
    EncodedCredentials = Binary.ToText(
        Text.ToBinary(Credentials), 
        BinaryEncoding.Base64
    ),
    
    Response = Web.Contents(
        "https://api.example.com",
        [
            RelativePath = "/v1/orders",
            Headers = [
                Authorization = "Basic " & EncodedCredentials
            ]
        ]
    ),
    
    JsonResponse = Json.Document(Response)
in
    JsonResponse

---

Handling Pagination

This is where most API integrations break down. Almost every production API paginates its results — it won't send you all 50,000 records in one response, it'll send you 100 at a time and make you ask for the rest. Power BI has no built-in pagination mechanism, so you have to build it yourself using recursive or iterative patterns in M.

Understanding Your API's Pagination Style

Before writing code, you need to know which pagination style your API uses:

Offset/Page-based pagination: The API accepts a page number or offset integer. You increment it on each call until you get fewer results than the page size (or until a flag in the response tells you you're done). GitHub, TMDB, and many REST APIs work this way.

Cursor/Token-based pagination: The API returns a cursor or next_page_token with each response. You pass that token back on the next request. This is common in newer APIs (Stripe, Salesforce, Twitter/X) and is more reliable for large, changing datasets because it doesn't skip records if data is inserted between pages.

Link header pagination: Some APIs (GitHub is one example) return a Link header in the HTTP response with the URL for the next page. This requires reading response headers, which Web.Contents can provide via Value.Metadata.

Offset-Based Pagination

The pattern here uses a List.Generate function to keep calling the API until the page comes back empty. Let's build this out properly using the TMDB API as our example — we'll fetch all popular movies across multiple pages:

let
    ApiKey = "your_api_key_here",
    BaseUrl = "https://api.themoviedb.org",
    
    // List.Generate keeps running until the stop condition is met
    // State: [Page = current page number, Data = last page's data, HasMore = continue?]
    AllPages = List.Generate(
        // Initial state
        () => [Page = 1, Data = {}, HasMore = true],
        
        // Condition: keep going while HasMore is true
        each [HasMore],
        
        // Next state: fetch the next page
        each 
            let
                PageNum = [Page],
                Response = Json.Document(
                    Web.Contents(
                        BaseUrl,
                        [
                            RelativePath = "/3/movie/popular",
                            Query = [
                                api_key = ApiKey,
                                language = "en-US",
                                page = Number.ToText(PageNum)
                            ]
                        ]
                    )
                ),
                Results = Response[results],
                TotalPages = Response[total_pages]
            in
                [
                    Page = PageNum + 1,
                    Data = Results,
                    HasMore = PageNum < TotalPages and List.Count(Results) > 0
                ],
        
        // Selector: what value to extract from each state
        each [Data]
    ),
    
    // Flatten all pages into a single list
    AllResults = List.Combine(AllPages),
    
    // Convert to table
    ResultTable = Table.FromList(AllResults, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(
        ResultTable,
        "Column1",
        {"id", "title", "release_date", "vote_average", "popularity", "overview"}
    )
in
    Expanded

Tip: Always include a safety limit in your pagination logic. If total_pages is unexpectedly 10,000 because of a bug or a data issue, you don't want Power BI to hammer the API for hours. Add a condition like PageNum < TotalPages and PageNum <= 50 to cap the total pages fetched.

Cursor-Based Pagination

Cursor-based pagination is slightly more complex because each request depends on data from the previous response. Here's how to handle it, using a generic pattern you can adapt to any cursor-based API:

let
    BaseUrl = "https://api.example.com",
    Token = "your_bearer_token",
    
    AllPages = List.Generate(
        // Initial state: no cursor yet, signal that we should fetch
        () => [Cursor = null, Data = {}, Continue = true],
        
        // Keep going while Continue is true
        each [Continue],
        
        // Fetch next page using the cursor from previous state
        each
            let
                CurrentCursor = [Cursor],
                
                // Build query params conditionally including cursor
                QueryParams = if CurrentCursor = null 
                    then [limit = "100"]
                    else [limit = "100", cursor = CurrentCursor],
                
                Response = Json.Document(
                    Web.Contents(
                        BaseUrl,
                        [
                            RelativePath = "/v1/transactions",
                            Query = QueryParams,
                            Headers = [Authorization = "Bearer " & Token]
                        ]
                    )
                ),
                
                NextCursor = Record.FieldOrDefault(Response, "next_cursor", null),
                Results = Response[data]
            in
                [
                    Cursor = NextCursor,
                    Data = Results,
                    Continue = NextCursor <> null and List.Count(Results) > 0
                ],
        
        each [Data]
    ),
    
    AllResults = List.Combine(AllPages),
    ResultTable = Table.FromList(AllResults, Splitter.SplitByNothing()),
    
    // Expand whichever fields your API returns
    Expanded = Table.ExpandRecordColumn(
        ResultTable,
        "Column1",
        {"id", "amount", "currency", "created_at", "status", "customer_id"}
    )
in
    Expanded

Record.FieldOrDefault is your friend here — it safely returns null (or any default you specify) if the field doesn't exist, which handles the last page gracefully when the API omits the cursor field rather than returning null.

---

Error Handling and Resilience

Production API connections need to handle failures gracefully. Networks hiccup. APIs return 429 (rate limited) or 503 (temporarily unavailable). Without error handling, one bad response breaks your entire refresh.

Using ManualStatusHandling

The ManualStatusHandling option in Web.Contents lets you intercept specific HTTP error codes and decide what to do with them, rather than letting Power Query throw an uncatchable error:

let
    Response = Web.Contents(
        "https://api.example.com",
        [
            RelativePath = "/v1/data",
            Headers = [Authorization = "Bearer " & Token],
            ManualStatusHandling = {400, 429, 500, 503}
        ]
    ),
    
    ResponseMetadata = Value.Metadata(Response),
    StatusCode = ResponseMetadata[Response.Status],
    
    Result = if StatusCode = 200 
        then Json.Document(Response)
        else error Error.Record(
            "API Error",
            "Received HTTP " & Number.ToText(StatusCode),
            ResponseMetadata
        )
in
    Result

Wrapping Calls in try...otherwise

For pagination loops where a single page failure shouldn't crash everything, wrap individual page fetches in try...otherwise:

let
    SafeFetch = (pageNum as number) =>
        let
            Result = try Json.Document(
                Web.Contents(
                    "https://api.example.com",
                    [
                        RelativePath = "/v1/records",
                        Query = [page = Number.ToText(pageNum), per_page = "100"],
                        Headers = [Authorization = "Bearer " & Token]
                    ]
                )
            ) otherwise null
        in
            Result
in
    SafeFetch

---

Configuring Automated Refresh

Getting the query to work in Power BI Desktop is only half the job. Making it refresh automatically in the Power BI service — every day, every hour, without anyone touching it — requires a few additional steps that many tutorials gloss over.

Step 1: Publish to Power BI Service

Once your query is working correctly in Desktop, publish the report to a workspace in the Power BI service. Go to the Home ribbon and click Publish, then select your target workspace.

Step 2: Install and Configure the On-Premises Data Gateway (If Needed)

Here's the part that surprises people: even though REST APIs are on the internet (not on your local network), Power BI service sometimes requires an on-premises data gateway to refresh Web connections. This depends on your organization's settings and the type of authentication used.

If your API uses credentials that need to be stored securely (Bearer tokens, API keys passed as headers), you'll typically need either:

• On-premises data gateway (Standard mode): A Windows service installed on a machine that stays on, acting as a broker between Power BI service and your data sources. Best for enterprise deployments.
• Personal mode gateway: Simpler, runs on your machine, but only supports your own reports. Not appropriate for shared/team dashboards.

To install: download the gateway installer from the Power BI service (Settings → Manage Gateways → Download Gateway). Install it on a machine that will remain on and connected.

Tip: If your API key is passed as a query parameter in the URL (not a header), and you've used the anonymous authentication type in Power BI Desktop when setting up the connection, you may be able to refresh without a gateway. But for anything involving custom headers or sensitive credentials, use a gateway.

Step 3: Set Up the Data Source in Gateway

After installing the gateway, add your data source to it:

1. Go to Power BI service → Settings → Manage Connections and Gateways
2. Select your gateway, then click New → Data source
3. Set the data source type to "Web"
4. Enter the base URL (just the domain: https://api.example.com)
5. Set the authentication method — use Basic, or Anonymous if your auth is handled in M code via headers
6. Save and test the connection

Step 4: Configure Scheduled Refresh

In the Power BI service, navigate to your dataset (now called Semantic Model in newer versions):

1. Click the three-dot menu next to your dataset and select Settings
2. Expand the "Scheduled refresh" section
3. Toggle it on
4. Choose your refresh frequency — options range from daily to every 30 minutes (with a Premium license)
5. Set your timezone and specific times
6. Optionally, add email addresses for refresh failure notifications

Warning: Power BI Pro limits you to 8 scheduled refreshes per day. Premium Per User and Premium capacity allow up to 48 per day. If you need near-real-time data, consider using DirectQuery mode with a supported API connector, or Power BI Dataflows, which have their own refresh schedules and can feed multiple reports.

Step 5: Map the Data Source

Power BI needs to match the data source in your published report to the data source configured in your gateway. If the URLs don't match exactly, refresh will fail with a "data source not found" error.

Go to your dataset settings → Data Source Credentials and confirm that the connection is mapped to your gateway data source. If it shows "Not Configured," click the pencil icon to assign it.

---

Hands-On Exercise

Let's build a complete, working solution from scratch. We'll connect to the Open-Meteo weather API (completely free, no API key needed) and pull a 90-day historical temperature dataset for New York City, with a structure ready for automatic daily refresh.

What we're building: A Power BI dataset that pulls the last 90 days of daily high and low temperatures for New York City, refreshes daily, and is ready to visualize as a trend chart.

Step 1: Open Power BI Desktop and launch Power Query Editor

Go to Home → Transform Data to open the Power Query Editor, then select New Source → Blank Query.

Step 2: Create the base query

Paste the following into the Advanced Editor (View → Advanced Editor):

let
    // Calculate date range dynamically
    EndDate = Date.From(DateTime.LocalNow()),
    StartDate = Date.AddDays(EndDate, -90),
    
    FormatDate = (d as date) => Date.ToText(d, "yyyy-MM-dd"),
    
    // Call Open-Meteo API
    Response = Json.Document(
        Web.Contents(
            "https://archive-api.open-meteo.com",
            [
                RelativePath = "/v1/archive",
                Query = [
                    latitude = "40.7128",
                    longitude = "-74.0060",
                    start_date = FormatDate(StartDate),
                    end_date = FormatDate(EndDate),
                    daily = "temperature_2m_max,temperature_2m_min",
                    timezone = "America/New_York",
                    temperature_unit = "fahrenheit"
                ]
            ]
        )
    ),
    
    // Extract daily data
    DailyData = Response[daily],
    Dates = DailyData[time],
    MaxTemps = DailyData[temperature_2m_max],
    MinTemps = DailyData[temperature_2m_min],
    
    // Zip into rows
    Rows = List.Zip({Dates, MaxTemps, MinTemps}),
    
    // Convert to table
    ResultTable = Table.FromRows(Rows, {"Date", "TempMax_F", "TempMin_F"}),
    
    // Set data types
    TypedTable = Table.TransformColumnTypes(
        ResultTable,
        {
            {"Date", type date},
            {"TempMax_F", type number},
            {"TempMin_F", type number}
        }
    ),
    
    // Add a derived column for temperature range
    WithRange = Table.AddColumn(
        TypedTable,
        "TempRange_F",
        each [TempMax_F] - [TempMin_F],
        type number
    )
in
    WithRange

Step 3: Name and load the query

In the Query Settings panel on the right, rename the query to NYCWeather_90Days. Click Close & Apply.

Step 4: Build a quick visualization

In the report canvas, add a Line Chart. Set the X axis to Date, add TempMax_F and TempMin_F as values, and TempRange_F as a tooltip field. You should see a clean 90-day temperature trend.

Step 5: Publish and configure refresh

Publish to your workspace. In the Power BI service, find the dataset, open settings, and enable scheduled refresh for once daily at 6:00 AM. Because Open-Meteo doesn't require authentication and the base URL is static, this should work without a gateway (Power BI service can reach public internet endpoints directly in many tenant configurations).

Each day, when the refresh runs, Date.From(DateTime.LocalNow()) will calculate a new end date, and Date.AddDays(EndDate, -90) will slide the window forward automatically. No manual intervention needed.

---

Common Mistakes & Troubleshooting

"Formula.Firewall: Query references other queries or steps..."

This is the most common error when building dynamic API calls. It happens when Power Query can't combine data sources across privacy boundaries. The fix is usually one of:

• Set all data source privacy levels to "Organizational" (File → Options → Current File → Privacy → Ignore Privacy Levels). Only do this in controlled environments.
• Refactor your query so that the authentication credentials (token fetching) and data fetching happen in the same query step rather than separate queries.
• Use a parameter (which is treated differently from a query reference) to pass the API key.

"The data source URL doesn't match any configured data sources"

You built the URL dynamically by concatenating strings into the first argument of Web.Contents. The refresh engine sees a different URL than what's registered in the gateway. Refactor to use RelativePath and Query options so the base URL is always the root domain.

Pagination loop runs forever or times out

You forgot a stop condition that covers the case where the API returns an empty list. Always check both the continuation flag AND List.Count(results) > 0. Also add a hard page cap.

All data appears from page 1 only

Check whether your API requires the page number as a string or an integer. Power Query's Query record sends everything as text, which most APIs handle correctly, but some APIs with strict typing will ignore parameters they can't parse. Also verify the exact parameter name — some APIs use page, some use pageNumber, some use offset.

Authentication works in Desktop but fails on scheduled refresh

The credentials stored in Desktop are not automatically transferred to the service. After publishing, go to dataset settings in the Power BI service, navigate to Data Source Credentials, and re-enter your credentials there. If you're using custom header auth (like X-API-Key), make sure your gateway data source is configured with Anonymous auth (since the auth is handled in your M code, not by Power BI's credential manager).

API returns 429 (Too Many Requests) during pagination

Your pagination loop is calling the API too fast. Add a deliberate delay using Function.InvokeAfter or restructure to fetch fewer pages per refresh. Alternatively, switch to a dataflow that refreshes less frequently but caches results, and point your Power BI dataset at the dataflow.

JSON fields are missing on some pages

APIs don't always return the same shape on every page — especially if some records have optional fields. Use Record.FieldOrDefault(row, "fieldName", null) instead of direct field access when expanding records, or use Table.ExpandRecordColumn with explicit column names (which safely adds nulls for missing fields).

---

Summary & Next Steps

You've covered a lot of ground. You now understand how Web.Contents works under the hood and why the RelativePath/Query pattern matters for scheduled refresh. You can implement API key, Bearer token, and Basic authentication patterns in M. You can build pagination loops for both offset-based and cursor-based APIs. And you know the steps to publish, configure a gateway, and set up automated daily refresh in the Power BI service.

The hands-on exercise gave you a real, working dataset that slides its date window automatically — the kind of "set it and forget it" data pipeline that saves hours of manual work every week.

Where to go from here:

• Power BI Dataflows: If multiple reports need the same API data, build the API connection in a Dataflow (Power Query Online) instead of in individual reports. The dataflow handles the API call and caches results; reports query the dataflow. This also sidesteps many Formula.Firewall issues.
• Incremental refresh: For large APIs where pulling 90 days of data every night is wasteful, Power BI's incremental refresh policies let you only pull new/changed records. This requires a date-filterable API endpoint and careful setup, but it dramatically reduces refresh time and API load.
• Custom connectors: If you're building API connections that many people in your organization will use, consider building a Power BI custom connector using the Power Query SDK. This lets you package auth flows, pagination, and error handling into a reusable, certified connector that non-technical users can deploy without touching M code.
• Azure Functions as middleware: When an API has complex OAuth flows, rate limiting, or data transformation needs that are awkward in M, consider putting a lightweight Azure Function between Power BI and the API. Power BI calls the function (which is a simple HTTP endpoint), and the function handles all the complexity. This is overkill for most cases but elegant for genuinely complex integrations.

The skills you've built here transfer directly to any REST API you'll encounter in a professional environment. The patterns are the same whether you're pulling from Salesforce, Shopify, HubSpot, or a custom internal service. Master the authentication and pagination patterns, keep your base URLs stable, and your API-connected datasets will refresh cleanly without drama.