API Authentication with Web.Contents

Advanced

Pass API keys, Bearer tokens, and query parameters to authenticated REST APIs using the Web.Contents options record — including how to handle token refresh and avoid hardcoding secrets.

The Web.Contents Options Record

All authentication in Web.Contents flows through its second argument — an options record. The key fields:

| Field | Type | Purpose | |---|---|---| | Headers | record | HTTP request headers (API keys, Bearer tokens, Content-Type) | | Query | record | Query string parameters (appended to the URL) | | Content | binary | POST body | | RelativePath | text | Path appended to the base URL — required for dynamic endpoints that refresh in the Power BI Service | | ManualStatusHandling | list | HTTP status codes Power Query should return instead of raising an error | | ManualCredentials | logical | Set true to bypass Power Query's credential prompt | | Timeout | duration | Maximum time to wait for a response (e.g. #duration(0, 0, 30, 0) for 30 seconds) | | IsRetry | logical | Set true to ignore any cached response and re-fetch from the server | | ExcludedFromCacheKey | list | Header names that should not be included in the cache key (useful for volatile auth headers) | | ApiKeyName | text | Name of the API key parameter when using the Web API credential type |

API Key in a Header

Most REST APIs expect a key in a request header:

let
    ApiKey = "your-api-key-here",

    Response = Web.Contents("https://api.example.com/data", [
        Headers = [
            #"X-API-Key" = ApiKey,
            #"Accept" = "application/json"
        ],
        ManualCredentials = true
    ]),

    Json = Json.Document(Response)
in
    Json

ManualCredentials = true tells Power Query not to pop up its own credential dialog — you're handling authentication yourself via the headers.

Bearer Token (OAuth 2.0)

APIs using OAuth return a short-lived access token you include as a Bearer header:

let
    // Step 1: Request a token
    TokenResponse = Json.Document(
        Web.Contents("https://auth.example.com", [
            RelativePath = "oauth/token",
            Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
            Content = Text.ToBinary(
                Uri.BuildQueryString([
                    grant_type    = "client_credentials",
                    client_id     = "YOUR_CLIENT_ID",
                    client_secret = "YOUR_CLIENT_SECRET"
                ])
            ),
            ManualCredentials = true
        ])
    ),
    AccessToken = TokenResponse[access_token],

    // Step 2: Use the token in the data request
    Data = Json.Document(
        Web.Contents("https://api.example.com", [
            RelativePath = "v1/records",
            Headers = [
                Authorization = "Bearer " & AccessToken,
                #"Content-Type" = "application/json"
            ],
            ManualCredentials = true
        ])
    )
in
    Data

Note: adding any Content field — even an empty binary — converts the request from GET to POST. Use Uri.BuildQueryString to build form-encoded POST bodies from a record instead of manually concatenating &-separated strings.

Query String Parameters

Pass parameters in the Query field rather than concatenating them into the URL string — this handles URL encoding automatically:

// Good — automatic URL encoding, readable
Web.Contents("https://api.example.com/search", [
    Query = [
        q      = "power query",
        limit  = "50",
        page   = "1",
        format = "json"
    ],
    ManualCredentials = true
])

// Avoid — manual concatenation, fragile, no encoding
Web.Contents("https://api.example.com/search?q=power+query&limit=50")

The Query record values must be text — convert numbers with Text.From(...).

RelativePath — Dynamic Endpoints That Refresh

When a query refreshes in the Power BI Service, the engine evaluates the base URL at design time to determine which data-source credentials to apply. If you build the full URL dynamically (e.g. by concatenating a page number or resource ID), the service can't match it to a known data source and the refresh fails.

RelativePath solves this — the base URL stays static, and the dynamic portion goes into RelativePath:

// Good — base URL is static, dynamic path handled by RelativePath
Web.Contents("https://api.example.com", [
    RelativePath = "v2/orders/" & Text.From(orderId),
    Headers = [#"X-API-Key" = ApiKey],
    ManualCredentials = true
])

// Avoid — dynamic URL breaks scheduled refresh in the Power BI Service
Web.Contents("https://api.example.com/v2/orders/" & Text.From(orderId), [
    Headers = [#"X-API-Key" = ApiKey],
    ManualCredentials = true
])

Power Query automatically inserts a / between the base URL and RelativePath — don't include a leading slash yourself, or you'll end up with a double // in the URL.

RelativePath is especially important in pagination loops where the path changes on every iteration.

ManualStatusHandling — Graceful Error Responses

By default, Power Query raises an error for any non-2xx HTTP response. ManualStatusHandling accepts a list of status codes that should be returned as normal responses instead:

let
    Response = Web.Contents("https://api.example.com/users/42", [
        Headers = [#"X-API-Key" = ApiKey],
        ManualStatusHandling = {400, 404, 429, 500},
        ManualCredentials = true
    ]),

    Status = Value.Metadata(Response)[Response.Status],

    Result =
        if Status = 200 then Json.Document(Response)
        else if Status = 429 then error "Rate limited — retry later"
        else if Status = 404 then null
        else error "API returned HTTP " & Text.From(Status)
in
    Result

Common use cases:

404 — return null instead of failing when a record doesn't exist
429 — detect rate limiting and surface a clear error message
500 — log the failure and continue processing other rows instead of aborting the entire query

Without ManualStatusHandling, any of these status codes would immediately halt the query with a generic DataSource.Error.

Paginating Through Results

Combine authentication with pagination using a recursive or List.Generate approach:

let
    ApiKey = "your-api-key",
    PageSize = 100,

    FetchPage = (page as number) =>
        Json.Document(
            Web.Contents("https://api.example.com/orders", [
                Query = [
                    page     = Text.From(page),
                    per_page = Text.From(PageSize)
                ],
                Headers = [#"X-API-Key" = ApiKey],
                ManualCredentials = true
            ])
        ),

    // Fetch pages until an empty result comes back
    AllPages = List.Generate(
        () => [page = 1, data = FetchPage(1)],
        each List.Count([data][items]) > 0,
        each [page = [page] + 1, data = FetchPage([page] + 1)],
        each [data][items]
    ),

    AllItems = List.Combine(AllPages),
    Result   = Table.FromList(AllItems, Splitter.SplitByNothing()),
    Expanded = Table.ExpandRecordColumn(Result, "Column1",
        Record.FieldNames(Result[Column1]{0}))
in
    Expanded

Avoiding Hardcoded Secrets

Never commit API keys or client secrets directly in a query. Instead:

Power BI parameters — Store the key as a Power BI parameter. Users can update it in the service without touching M code.
Azure Key Vault — Use Web.Contents to fetch the secret from Key Vault at query time (requires managed identity or a service principal).
Environment variables via a config table — Keep secrets in a separate, permission-restricted query that other queries reference.

// Read the key from a dedicated Config query (not committed to source control)
let
    ApiKey   = Config[ApiKey],
    Response = Web.Contents("https://api.example.com/data", [
        Headers = [#"X-API-Key" = ApiKey],
        ManualCredentials = true
    ])
in
    Json.Document(Response)

Refresh Behavior in Power BI Service

ManualCredentials = true bypasses the credential store, so the key must be embedded or read from a parameter/config query that is itself accessible during refresh.
OAuth tokens are often short-lived. If the token expires between the token request and the data request (rare for scheduled refresh, possible for very large pulls), structure the query so the token fetch and data fetch happen in the same M evaluation.
Power BI Service enforces privacy level rules — if your API and your config query have different privacy levels, M may refuse to combine them. Set both to the same privacy level, or set the dataset to Ignore Privacy Levels (only for trusted, internal data).
Queries that combine a POST request (e.g. fetching an OAuth token) and a GET request (fetching data with that token) in the same query may fail to refresh in the Power BI Service because the engine detects a dynamic data source. Workarounds include using Dataflows, a Data Gateway with a custom connector, or splitting the token fetch into a separate query.