Power BI ships with over 200 native connectors, but the moment your data lives in a niche SaaS tool, an internal system, or a platform Microsoft has simply not built a connector for yet, you are on your own. The good news is that almost any REST API can still be brought into Power BI. It just takes a different route than clicking a named connector in the Get Data menu.

At Versich, we build these connections regularly for clients whose data sits in platforms without native Power BI support. This guide walks through the practical methods for pulling REST API data into Power BI, the authentication patterns you will run into, the M code needed to make it work, and where the approach breaks down at scale.

If you are also weighing whether a custom connector or a managed integration is the better long-term fit, our overview of Power BI architecture covers how connectivity choices affect refresh design and performance further down the line.

Why Power BI Has No Connector for Some Data Sources

Microsoft prioritises native connectors for high-volume, broadly used platforms such as Salesforce, Dynamics 365, and Google Analytics. Smaller SaaS products, internal company APIs, and newer platforms are rarely worth Microsoft's investment to build and certify a dedicated connector for, even when thousands of businesses use them.

This leaves three realistic options when you hit a missing connector:

• Use Power BI's generic Web connector to call the REST API directly inside Power Query
• Build a proper custom connector as a Power Query M extension if the integration needs to be reused widely or signed for governed distribution
• Stage the API data in an intermediate database first, then connect Power BI to that database instead of the API directly

For most business reporting needs, the first or third option is the right starting point. A full custom connector is worth the investment only when many people across an organisation need to reuse the same connection repeatedly.

Understanding How a REST API Works With Power Query

Before writing anything, it helps to understand what Power Query actually needs from an API. Every REST API exposes endpoints, URLs that return data when called, usually in JSON format. Power Query's Web.Contents function sends an HTTP request to that endpoint and returns the raw response, which you then parse into a table.

Method One: Calling the API Directly From Power Query

This is the fastest way to get API data into Power BI and works well for smaller datasets, one-off reports, or APIs with simple authentication. No coding environment outside Power BI Desktop is required.

Step 1: Open Get Data and select Web. In Power BI Desktop, go to Home, then Get Data, then search for Web and select it.

Step 2: Choose Advanced and enter the URL. Selecting Advanced gives you access to add custom headers, which most APIs require for authentication. Enter the base endpoint URL.

Step 3: Add authentication headers. If the API uses a bearer token, add an Authorization header with the value Bearer followed by your token. If it uses an API key, the header name and format will be specified in that API's documentation.

Step 4: Connect and parse the JSON. Power Query returns the raw response. Click into the resulting record or list, then use Convert to Table and expand the columns you need.

Step 5: Promote headers and set data types. Once expanded into a flat table, set correct data types for each column so downstream DAX calculations behave correctly.

Here is a minimal example connecting to an API that uses a bearer token passed in the request header:

let
    apiUrl = "https://api.example.com/v1/orders",
    token = "YOUR_BEARER_TOKEN",
    Source = Json.Document(
        Web.Contents(
            apiUrl,
            [
                Headers = [
                    #"Authorization" = "Bearer " & token,
                    #"Content-Type" = "application/json"
                ]
            ]
        )
    ),
    ordersList = Source[data],
    ordersTable = Table.FromList(ordersList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expanded = Table.ExpandRecordColumn(ordersTable, "Column1", {"id", "customer", "total", "created_at"})
in
    expanded

This pattern works for any REST API that returns a JSON array under a known key, with adjustments to the field names expanded in the final step.

Handling Authentication: API Keys, Bearer Tokens, and OAuth

Authentication is where most REST API connections in Power BI either work cleanly or fall apart. The method you need depends entirely on what the API supports.

API key in query string or header: The simplest case. The key is either appended to the URL as a parameter or passed as a custom header. This works reliably in basic Web.Contents calls with no special handling needed.

Static bearer token: Common for internal APIs or services with long-lived tokens. Pass the token in the Authorization header as shown in the example above. This breaks if the token expires and is not refreshed manually.

OAuth 2.0: The most common pattern for modern SaaS APIs such as Salesforce-adjacent tools, HubSpot, or QuickBooks Online. OAuth requires an initial authorization step where the user grants access, followed by an access token that expires (often within an hour) and a refresh token used to get a new one. Power Query alone cannot easily manage this token refresh cycle inside a simple query. This is the point where most teams either build a full custom connector with OAuth handlers, or stage the data through an intermediate service that manages token refresh on a schedule.

For OAuth-protected APIs specifically, our experience is that staging the data through Azure SQL using a script that handles token refresh independently of Power BI is far more reliable than trying to manage OAuth entirely inside Power Query.

Handling Pagination So You Get All the Data

Most REST APIs do not return every record in a single response. They paginate results, either using page numbers, offset and limit parameters, or a cursor token returned in each response that points to the next page.

Power Query handles this using the List.Generate function to repeatedly call the API until no more pages remain. Here is a pattern for cursor-based pagination:

let
    GetPage = (cursor as nullable text) =>
        let
            url = "https://api.example.com/v1/contacts" &
                  (if cursor <> null then "?after=" & cursor else ""),
            response = Json.Document(
                Web.Contents(url, [Headers = [#"Authorization" = "Bearer " & token]])
            )
        in
            response,

    AllPages = List.Generate(
        () => [result = GetPage(null), cursor = null],
        each [result] <> null,
        each [result = GetPage([result][next_cursor]?), cursor = [result][next_cursor]?],
        each [result][data]
    ),

    Combined = List.Combine(AllPages),
    FinalTable = Table.FromList(Combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    FinalTable

The exact field names for the cursor and data array will differ by API, but the structure of calling repeatedly until the cursor field comes back empty is consistent across most modern REST APIs.

Method Two: Staging API Data in a Database First

For production reporting, calling an API directly from Power Query has real limitations. Refreshes are slower because the API call happens every time the report refreshes, OAuth token management becomes fragile, and rate limits can cause refresh failures during business hours when multiple users trigger refreshes close together.

The more durable approach is to extract data from the API on a schedule using a script outside Power BI, store it in a database such as Azure SQL, and then have Power BI connect to that database using the native SQL Server connector instead of the API directly.

This is the model our own data integrations follow for clients connecting platforms without a native Power BI connector. We automatically extract the API data on a schedule into a database you own, clean and standardise it, and hand you a connection that behaves exactly like any native SQL Server source inside Power BI.

Refreshing API-Connected Reports in the Power BI Service

A query that works fine in Power BI Desktop can still fail once published, because the Power BI Service needs a way to reach the API or database on a schedule without you sitting at your machine.

Cloud APIs: If the API endpoint is publicly accessible over the internet, the Power BI Service can call it directly during scheduled refresh, provided the authentication method does not require manual interaction (which rules out most OAuth flows unless handled via a service principal).

Internal or firewalled APIs: These require an on-premises data gateway, the same component used for connecting Power BI to on-premises SQL Server. The gateway runs on a machine inside your network and relays the request.

Database staging layer: If you have staged the data in Azure SQL, refresh becomes simple and reliable, since Power BI is connecting to a standard cloud database rather than managing API authentication itself during refresh.

Common Mistakes When Connecting REST APIs to Power BI

A few patterns come up repeatedly when teams attempt this without prior experience.

Hardcoding tokens directly into M code: Static credentials embedded in a query are a security risk and break the moment the token rotates. Use Power BI's credential management through the data source settings rather than embedding secrets in the query text.

Ignoring pagination entirely: A query that only fetches the first page of results will silently under-report data with no error message, which is far more dangerous than an outright failure.

Not handling rate limits: Calling an API too aggressively, especially across multiple report refreshes triggered close together, can result in your API key being temporarily blocked, breaking every report that depends on it.

Building everything inside one giant query: Authentication, pagination, and transformation logic combined into a single unreadable M expression becomes very difficult to debug when the API changes. Breaking the logic into named functions inside Power Query makes maintenance far easier.

Assuming OAuth works the same as a static token: OAuth's expiring access tokens need an active refresh mechanism. Treating it like a permanent API key is the single most common reason these integrations break weeks after going live.

How Versich Approaches REST API Connections to Power BI

Most engagements start with identifying exactly which data the reporting actually needs, not just connecting everything the API exposes. From there we choose between a direct Power Query connection for simple, low-volume cases, or a managed database staging layer for anything involving OAuth, larger datasets, or refresh reliability requirements.

A typical project covers API documentation review and authentication setup, building and testing the extraction logic, handling pagination and error cases properly, and either a Power BI Desktop handover or a fully managed Azure SQL pipeline depending on the client's needs.

For teams who want a deeper read on choosing between direct API calls and a staged integration layer, our guide on Power BI data modelling best practices covers why a clean intermediate data layer tends to outperform direct source queries as report complexity grows.

To learn more about how our Power BI team works with clients, visit our Power BI services page.

Conclusion

A missing native connector does not mean a data source is out of reach for Power BI. Between direct Power Query calls for simple cases, proper pagination and authentication handling for more complex APIs, and a database staging layer for production-grade reliability, there is a workable path for almost any REST API.

At Versich, we have connected dozens of platforms without native Power BI support into client reporting environments, choosing the right method based on data volume, authentication complexity, and how many people need to rely on the connection. If you have a data source Power BI does not natively support, contact our team and we will help you find the right way to bring it in.