Use API REST as source in Power BI Report Server

MarkTower — Thu, 24 Apr 2025 11:42:07 GMT

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

Re: Use API REST as source in Power BI Report Server

SolomonovAnton — Thu, 24 Apr 2025 19:17:43 GMT

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.

Re: Use API REST as source in Power BI Report Server

MarkTower — Fri, 25 Apr 2025 05:29:11 GMT

Thanks SolomonovAnton for so detailed answer!! very usefull

topic Use API REST as source in Power BI Report Server in Report Server

Use API REST as source in Power BI Report Server

Re: Use API REST as source in Power BI Report Server

✅ What’s Possible

⚠️ Key Considerations

1. Authentication

2. Data Size and Performance

3. Gateway is NOT needed

4. Custom Headers and Query Parameters

5. M Code Example

✅ Best Practices

Re: Use API REST as source in Power BI Report Server