- Get link
- X
- Other Apps
This article explains in depth how to authenticate against Web APIs from Power Query in Excel and Power BI, covering API keys, Basic and Bearer tokens, OAuth 2.0, Azure AD, and practical M code patterns that work reliably in both Desktop and online refresh scenarios.
1. Why Web API authentication matters in Power Query
Power Query is often the easiest way for analysts to consume Web APIs directly from Excel or Power BI without writing full applications.
However, most production APIs are protected by some form of authentication, and misunderstanding how authentication interacts with the Web connector, credentials storage, gateways, and the Power BI service is a common cause of failed refreshes and 401 errors.
To design a reliable solution, you must treat authentication as part of your data model, not just something you type once in a dialog.
- Decide which authentication method the API supports (API key, Basic, OAuth 2.0, organizational account, etc.).
- Know whether the credentials are user-specific or app-specific and how often they expire.
- Implement the authentication pattern in M code so that it works for scheduled refresh, not only in Desktop.
2. Authentication options in the Web connector
When you use the Web connector in Power Query (Excel, Power BI Desktop, or Power Query Online), you are prompted by the “Access Web content” dialog to select an authentication method and the scope (level) at which it applies, for example the whole host such as https://api.contoso.com/ or a more specific path.
2.1 Built-in authentication methods
The exact labels differ slightly between products, but common options in the Web connector include:
- Anonymous.
- Basic (username and password).
- Windows (integrated / NTLM) for on-premises endpoints.
- Web API or Key (API key in header or parameter, depending on connector).
- Organizational account (Microsoft Entra ID / Azure AD) for some SaaS services.
These options are implemented at the connector level and stored in Power Query’s credential store; they are not part of your M code.
2.2 Typical authentication methods for Web APIs
For generic REST APIs reached through the Web connector, the most common authentication patterns are:
- Anonymous plus custom headers (API key, Bearer token, etc.).
- Basic authentication (username and password encoded as an Authorization header).
- Bearer token (OAuth 2.0 access token or static personal access token).
- Full OAuth 2.0 or OpenID Connect flow via a custom connector.
| Auth method | Typical scenario | Configured in UI or M | Service refresh reliability |
|---|---|---|---|
| Anonymous + API key header | Commercial APIs with simple key-based auth | Credentials set to Anonymous, key passed in Web.Contents headers | High, if key does not expire |
| Basic | Legacy or internal APIs with username/password | Either Basic in UI or Authorization header in M | High, but credentials must be rotated manually |
| Bearer token (static) | Personal access tokens or long-lived API tokens | Token stored in parameter or credential, sent via Authorization header | High until token expiry |
| OAuth 2.0 (interactive) | User-sign-in to SaaS APIs (Graph, custom apps) | Usually via custom connector with OAuth flow | High when implemented as custom connector |
| Windows / Organizational account | On-prem or Azure AD–protected OData / Web APIs | Configured in “Access Web content” dialog | High, assuming gateway and identity are correct |
Note : When using generic Web APIs, the most robust pattern is to set the data source authentication to Anonymous and manage Authorization and API-key headers explicitly in your
Web.Contents calls.3. The core pattern: Web.Contents with headers
Almost all Web API scenarios in Power Query rely on the Web.Contents function, which allows you to specify URL, query parameters, HTTP method, headers, and sometimes body payload.
3.1 Basic Web.Contents template
let BaseUrl = "https://api.contoso.com/v1/customers", Response = Web.Contents( BaseUrl, [ Query = [page = "1", pageSize = "100"], Headers = [ Accept = "application/json" ] ] ), Json = Json.Document(Response), Table = Table.FromRecords(Json) in Table This template is expanded for each authentication type by adding the appropriate headers and sometimes a first step to obtain a token.
3.2 Adding Authorization and API key headers
For many APIs, you combine an Authorization header and an API key header in the same request.
let ApiKey = Extension.LoadString("ApiKey"), // or a parameter Token = Extension.LoadString("BearerToken"), BaseUrl = "https://api.contoso.com/v1/customers", Response = Web.Contents( BaseUrl, [ Query = [page = "1", pageSize = "100"], Headers = [ #"Authorization" = "Bearer " & Token, #"x-api-key" = ApiKey, Accept = "application/json" ] ] ), Json = Json.Document(Response), Table = Table.FromRecords(Json) in Table Note : Power Query treats header names as case-insensitive, but many API examples use title case such as
Authorization or X-API-Key. Matching the case used in the provider documentation improves readability even if it is not required by Power Query.4. Implementing specific authentication methods
4.1 API key authentication with Anonymous credentials
A common recommendation for APIs protected by an API key or by a custom Authorization header is to set the Power Query data source credentials to Anonymous and then specify the key in the Headers or Query option.
4.1.1 API key in HTTP header
This pattern keeps the URL clean and avoids logging the key in gateway logs that capture URLs.
let ApiKey = "YOUR_API_KEY_HERE", BaseUrl = "https://api.contoso.com/v1/customers", Source = Json.Document( Web.Contents( BaseUrl, [ Headers = [ #"x-api-key" = ApiKey, Accept = "application/json" ] ] ) ), Table = Table.FromRecords(Source) in Table 4.1.2 API key as query parameter
Some APIs only support query-string keys such as ?apikey=....
let ApiKey = "YOUR_API_KEY_HERE", BaseUrl = "https://api.contoso.com/v1/customers", Source = Json.Document( Web.Contents( BaseUrl, [ Query = [ apikey = ApiKey, page = "1", pageSize = "100" ], Headers = [Accept = "application/json"] ] ) ), Table = Table.FromRecords(Source) in Table Note : Avoid hard-coding API keys inside queries that will be shared or checked into source control. Store secrets in parameters, environment variables, or in a custom connector so they can be managed separately from M code.
4.2 Basic authentication
Many legacy or internal APIs are secured with Basic authentication, where the client sends Authorization: Basic <base64(username:password)> on each request.
You can configure Basic authentication directly in the “Access Web content” dialog, but in some scenarios you may need to build the header manually, for example if the API expects additional headers or if you combine Basic with another token.
4.2.1 Basic authentication from the UI
- In Excel or Power BI Desktop, choose Get Data > From Web and paste the API URL.
- When the “Access Web content” dialog appears, choose Basic.
- Enter the username and password supplied by the API provider.
- Select the proper level (for example
https://api.contoso.com/) so you can reuse the same credentials for other endpoints under that host.
4.2.2 Basic authentication via Authorization header in M
If you want full control, you can compute the Basic header in M code.
let Username = "myuser", Password = "mypassword", CredentialsText = Username & ":" & Password, CredentialsBin = Text.ToBinary(CredentialsText), CredentialsB64 = Binary.ToText(CredentialsBin, BinaryEncoding.Base64), AuthHeader = "Basic " & CredentialsB64, BaseUrl = "https://api.contoso.com/v1/customers", Response = Web.Contents( BaseUrl, [ Headers = [ #"Authorization" = AuthHeader, Accept = "application/json" ] ] ), Json = Json.Document(Response), Table = Table.FromRecords(Json) in Table 4.3 Bearer token (personal or static access token)
Bearer tokens are often used for personal API tokens or for token-based SaaS APIs where you manually generate a token on a management page and paste it into Power Query.
The pattern is straightforward: store the token securely, then send it in the Authorization header.
let Token = "YOUR_LONG_BEARER_TOKEN", BaseUrl = "https://api.contoso.com/v1/customers", Source = Json.Document( Web.Contents( BaseUrl, [ Headers = [ #"Authorization" = "Bearer " & Token, Accept = "application/json" ] ] ) ), Table = Table.FromRecords(Source) in Table Note : Treat Bearer tokens like passwords. Do not embed them directly in queries in shared workspaces. Prefer parameters protected as “sensitive” and review who has permission to edit the dataset or dataflow.
4.4 OAuth 2.0 and Azure AD (Microsoft Entra ID)
Modern APIs typically use OAuth 2.0 and sometimes OpenID Connect. In this model, Power Query must acquire an access token by following an OAuth flow and then use that token in API calls, usually via an Authorization header.
The most reliable way to integrate a custom OAuth 2.0 API with Power Query is to build a custom connector that declares an OAuth authentication record and handles login, refresh, and logout.
4.4.1 Token acquisition followed by data call
In some advanced scenarios, you can implement a client-credentials or resource-owner-password flow directly in M, especially for internal APIs where storing the client secret in a gateway is acceptable.
A common pattern is:
- Call the OAuth token endpoint with
Web.Contentsusing a POST body and appropriate headers. - Parse the JSON response to extract
access_token. - Call the protected API with an Authorization header
Bearer <access_token>.
The simplified pattern looks like this.
let // 1. Obtain token TenantId = "YOUR_TENANT_ID", ClientId = "YOUR_CLIENT_ID", ClientSecret = "YOUR_CLIENT_SECRET", Scope = "api://your-api/.default", TokenUrl = "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token",
TokenResponse = Json.Document(
Web.Contents(
TokenUrl,
[
Content = Text.ToBinary(
"client_id=" & ClientId &
"&client_secret=" & ClientSecret &
"&grant_type=client_credentials" &
"&scope=" & Uri.EscapeDataString(Scope)
),
Headers = [
#"Content-Type" = "application/x-www-form-urlencoded"
],
ManualStatusHandling = {400, 401, 403}
]
)
),
AccessToken = TokenResponse[access_token],
// 2. Call API with Bearer token
ApiUrl = "https://api.contoso.com/v1/customers",
ApiResponse = Json.Document(
Web.Contents(
ApiUrl,
[
Headers = [
#"Authorization" = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
),
ResultTable = Table.FromRecords(ApiResponse)
in
ResultTable
Note : For public or multi-tenant APIs, and for any scenario that requires delegated user sign-in, you should prefer a proper custom connector with OAuth 2.0 support instead of embedding client secrets or refresh tokens in M code.
5. Excel, Power BI Desktop, and online refresh
Power Query behaves slightly differently depending on whether you run it in Excel, Power BI Desktop, Power BI dataflows, or Power Query Online.
5.1 Web and Web API connectors in Power Query Online
On Power Query Online (for example in dataflows or Fabric), the Get Data experience distinguishes between “Web page” and “Web API” connectors; both ultimately use the same Web.Contents infrastructure but expose slightly different configuration surfaces and authentication options.
- “Web page” is oriented toward HTML scraping and tables.
- “Web API” assumes a JSON or XML response and more formal API-style interaction.
- Both allow you to choose authentication method and on-premises data gateway where needed.
5.2 Gateway and credential storage
For scheduled refresh, the credentials you configure in Desktop must be reconfigured in the Power BI service. The service stores them separately from your PBIX file, and for on-premises endpoints you must bind them to a gateway that can reach the API endpoint.
When refresh errors occur in the service but not in Desktop, it is often because:
- The gateway cannot resolve or reach the API URL.
- The authentication method in the service is different from the one used in Desktop.
- The token or password stored in the service has expired.
5.3 Excel-specific behaviors and pitfalls
Recent versions of Excel may issue multiple HTTP requests for a single Web query, which can create subtle issues when APIs expect a custom Authorization header on every request. In some cases, a first request includes the Authorization header, but a second request triggered by navigation does not, resulting in 401 responses.
When you see queries that work in Power BI Desktop but fail in Excel with 401 errors, it is important to capture the traffic with a proxy or server logs and verify whether headers are consistently sent.
6. Troubleshooting common authentication errors
6.1 Login prompts looping or reappearing
If users are repeatedly prompted with the “Access Web content” dialog, it usually means Power Query has stored credentials with the wrong scope or method. You can correct this by editing data source settings.
- In Power BI Desktop or Excel, go to File > Options and settings > Data source settings.
- Switch to Global permissions if the source is used across multiple files.
- Select the relevant URL or host and choose Edit Permissions.
- Change the authentication method or clear permissions and reconnect.
- Ensure the “level” (scope) of the credentials matches the base URL you use in your queries.
6.2 401 Unauthorized
Common causes include:
- Missing or misspelled Authorization or API-key headers.
- Token expired or revoked.
- Credentials stored at a different URL scope than the one used by
Web.Contents. - Excel issuing a second request without headers (in some builds).
Checklist.
- Check the API logs to see exactly which headers Power Query has sent.
- Confirm that the token or key still works using a tool like curl or Postman.
- Verify that you are not mixing HTTP and HTTPS, or mismatched hostnames.
- In complex scenarios, consider using a reverse proxy to normalize requests from Power Query before they hit the API.
6.3 403 Forbidden
403 errors usually mean the credentials are valid but do not have permission for the requested resource, or that the API is blocking certain IP ranges.
- For SaaS APIs, verify that the user, service principal, or token has the required roles or scopes assigned.
- If you use a gateway, ensure the API is configured to accept requests from the gateway’s public IP addresses.
- Check that you are not attempting to use user-based credentials where app-only credentials are required, or vice versa.
6.4 429 and throttling issues
When calling OAuth-protected APIs at scale, you may encounter 429 responses or throttling. For some APIs, you can mitigate this in Power Query by caching tokens and using parameters such as ExcludedFromCacheKey on Web.Contents so token values do not cause needless cache misses.
Note : For high-volume or mission-critical integrations, it is often better to expose a stable OData or REST façade specifically designed for Power Query rather than pointing it directly at a heavily rate-limited public API.
FAQ
Which authentication method should I use first for a new Web API in Power Query?
Start by reading the API documentation and identifying exactly which authentication mechanism it supports. If it uses a simple API key or static token, the easiest pattern in Power Query is to configure the data source as Anonymous and pass the key or token via headers in Web.Contents. If the API uses OAuth 2.0 or OpenID Connect, plan to implement a custom connector or use an existing certified connector whenever possible.
Can I use OAuth 2.0 with Power Query without building a custom connector?
In limited cases you can implement a client credentials or similar token flow directly in M by calling the token endpoint and then using the access token in a Bearer header. This approach is appropriate mainly for internal or low-risk scenarios because you must store client secrets or credentials in the gateway. For public or multi-tenant APIs that require secure handling of refresh tokens and user consent, a custom connector with a defined OAuth authentication record is strongly recommended.
Why does my query work in Power BI Desktop but fail in the Power BI service?
The most common differences are credentials and network path. Power BI Desktop runs under your interactive identity and network, while the service uses stored credentials and possibly an on-premises data gateway. Confirm that the authentication method and credentials are configured in the service, that the gateway can resolve and reach the API URL, and that any tokens or keys you hard-coded in M are still valid. Also verify that you are not relying on Windows authentication when the service is using a different identity.
Is it safe to hard-code API keys or tokens in M code?
Hard-coding secrets in M code is strongly discouraged. Anyone with permission to edit the query can see the secret, and if the PBIX or Excel file is shared or checked into source control, those secrets will leak. Use parameters marked as sensitive, secure storage mechanisms provided by the service, or a custom connector that hides secrets from the query code.
How do I change or clear Web API credentials in Power Query?
In Excel or Power BI Desktop, open Data Source Settings, locate the Web or Web API data source, and use Edit Permissions or Clear Permissions to modify or remove stored credentials. After clearing, reconnect to the API and choose the correct authentication method and scope. In the Power BI service, you perform a similar operation on the dataset or dataflow settings under the Data source credentials section.