How to Consume a REST API in Power BI with Power Query

Connecting Power BI directly to a REST API lets you pull always-fresh data from web services — CRMs, online stores or marketing tools — without manual exports. Power Query handles the HTTP request, converts the JSON response into a table and keeps everything ready for automatic refresh. Consuming a REST API in Power BI is simpler than it looks once you know the right pattern with the Web.Contents function.

Prerequisites

• Power BI Desktop installed (a recent version).
• The base address (URL) of the API and the endpoint you want to query.
• An API key or access token, if the API requires authentication.
• Basic knowledge of the Power Query Editor (Home > Transform data).

Step 1: Open Power Query and create a Web query

In Power BI Desktop, click Get data > Web. Choose the Advanced option so you can separate the base address from the rest of the request — this detail matters so that scheduled refresh works later in the Power BI Service. Put the base address in the first URL part and the endpoint path in the URL parts field.

Once you confirm, Power Query automatically creates a step with the Web.Contents function. You will edit it by hand to gain full control over headers and parameters.

Step 2: Write the request with Web.Contents

Open the Advanced Editor (Home > Advanced Editor) and replace the code with the pattern below. Notice that the base address stays fixed while everything else goes into RelativePath and Query — this way Power BI recognises the data source and allows refresh in the cloud.

let
    Response = Json.Document(
        Web.Contents(
            "https://api.example.com",
            [
                RelativePath = "v1/sales",
                Headers = [#"Authorization" = "Bearer YOUR_TOKEN", #"Accept" = "application/json"],
                Query = [ page = "1", per_page = "100" ]
            ]
        )
    )
in
    Response

The Json.Document function converts the text response into a record or a list that Power Query already knows how to read. If your API uses a key instead of a token, change the Authorization header to the name the documentation indicates (for example, x-api-key).

Step 3: Turn the JSON into a table

Most APIs return the records inside a field, for example data. Extract that list and turn it into a table with readable columns:

let
    Response = Json.Document(Web.Contents("https://api.example.com",
        [ RelativePath = "v1/sales",
          Headers = [#"Authorization" = "Bearer YOUR_TOKEN"],
          Query = [ page = "1", per_page = "100" ] ])),
    Items = Response[data],
    ToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(ToTable, "Column1", {"id", "customer", "total"})
in
    Expanded

Adjust the names id, customer and total to the real fields of your API. From here you can use the Power Query graphical interface to change data types, remove columns and filter rows.

Step 4: Handle pagination

APIs rarely return everything at once. To go through all pages use List.Generate, which repeats the request until a page comes back empty:

let
    GetPage = (p as number) as list =>
        let
            r = Json.Document(Web.Contents("https://api.example.com",
                [ RelativePath = "v1/sales",
                  Headers = [#"Authorization" = "Bearer YOUR_TOKEN"],
                  Query = [ page = Text.From(p), per_page = "100" ] ]))
        in
            r[data],
    Pages = List.Generate(
        () => [i = 1, rows = GetPage(1)],
        each List.Count([rows]) > 0,
        each [i = [i] + 1, rows = GetPage([i] + 1)],
        each [rows]
    ),
    AllRows = List.Combine(Pages)
in
    AllRows

The result is a single list with the records from every page, ready to convert into a table just like in the previous step. Adapt the stop condition to your API: some return a next field or the total page count, which you can use instead of checking whether the list is empty.

Security tip: avoid writing the token directly in the code. In production, when Power BI asks for credentials, choose Web API key or OAuth. That way the key is stored securely and does not travel inside the .pbix file.

Verify the result

Check that the expanded column shows the expected data in the Power Query preview. Click Close & Apply and build a simple visual, such as a table, to see the rows in the report. Finally, test the refresh with Home > Refresh: if the data comes back without credential errors, the request is correctly configured. If you get the common error "Access to the resource is forbidden", review the token and the Authorization header.

Conclusion

With Web.Contents, Json.Document and List.Generate you have everything you need to turn almost any REST API into a refreshable data source in Power BI. The next step is to publish the report to the Power BI Service and set up scheduled refresh — did you notice how the RelativePath pattern makes that possible? Try it now with a public API, for example one for exchange rates or weather, to practise without depending on credentials.