Use API REST as source in Power BI Report Server

[MarkTower]

Hi

I want to load data into my report in Power BI Report Server from an API REST. In this document Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn apparently it's supported ("web" source) using import mode (as far as i understand cached data means import mode) and even it could has an scheduled refresh. Has anyone used this connector to load data from APIs? are there any concerns that i should be aware if we try it?

Thanks in advance

best regards

[MarkTower]

Thanks @SolomonovAnton  for so detailed answer!! very usefull

[SolomonovAnton · Accepted solution]

You're absolutely right in your interpretation — Power BI Report Server does support Web data sources (which includes REST APIs) in Import mode, and yes, scheduled refreshes can be configured for them. However, there are some nuances and limitations to keep in mind.

Here’s a step-by-step overview and some important considerations:

---

✅ What’s Possible

• Using Web connector (Power Query): You can use the Web.Contents function in Power Query to call REST APIs.

• Import mode only: DirectQuery isn’t supported for Web sources in Power BI Report Server.

• Scheduled refresh support: You can schedule refreshes on PBIRS, but there are caveats (see below).

---

⚠️ Key Considerations

1. Authentication

• Anonymous or Basic Auth works fine.

• OAuth 2.0 is not supported on PBIRS for web sources — this is a major limitation if your API uses modern authentication protocols.

• Workaround: If possible, use an intermediate proxy/API gateway that handles OAuth and exposes a simpler authenticated endpoint to PBIRS.

2. Data Size and Performance

• REST APIs are typically paginated. You’ll need to handle pagination manually in Power Query, which can make refreshes slow.

• Avoid overly frequent refreshes or large datasets due to performance limits of PBIRS.

3. Gateway is NOT needed

• Unlike Power BI Service, you don’t need an On-Premises Gateway for scheduled refresh in PBIRS — the server itself handles refresh.

4. Custom Headers and Query Parameters

• Web.Contents allows for custom headers (like tokens) and query parameters — but you must avoid dynamic parts in the URL path that rely on parameters or variables evaluated at runtime, or refresh might fail.

5. M Code Example

Here’s a sample M script to get data from a paginated API:

let
    GetPage = (Page as number) =>
        let
            Source = Json.Document(Web.Contents("https://api.example.com/data", [
                Query = [page = Text.From(Page)],
                Headers = [#"Authorization" = "Bearer YOUR_TOKEN"]
            ])),
            Data = Source[data]
        in
            Data,

    PageList = List.Generate(() => 1, each _ <= 5, each _ + 1),
    AllData = List.Combine(List.Transform(PageList, each GetPage(_))),
    Table = Table.FromList(AllData, Record.FieldValues, {"Column1", "Column2", ...})
in
    Table

✅ Best Practices

• Cache results in a staging database if refresh performance becomes an issue.

• Log API call responses for audit/debug purposes.

• Document token expiration and re-auth workflows if applicable.