Forum Discussion
Power BI Online - Cannot Set up Automatic Refresh for REST API Source Without Gateway
Hi komyji
Thanks for sharing this it's a common issue when using REST APIs in Power BI Service, even when the API is public and works perfectly in Power BI Desktop.
Now, why this happens and how you can resolve it without needing an on-premises data gateway.
Power BI Service performs a security check to detect dynamic data sources — and this includes any queries that build URLs dynamically, such as
Pagination via List.Generate
Using Web.Contents() with RelativePath inside a function
Dynamically constructed query strings or endpoints
Once a query is flagged as dynamic, Power BI disables direct cloud refresh and requires a gateway, even for public endpoints. This behavior is confirmed by Microsoft and discussed in multiple community threads.
How to Fix It (No Gateway Required)
Use Web.Contents with a static base URL, and pass pagination and filters through RelativePath and Query parameters. This makes your query predictable and certifiable, reducing the chance of being flagged as dynamic.Example
Web.Contents(
"https://gis.brno.cz",
[
RelativePath = "ags1/rest/services/Hosted/cyklo_meteo_merged/FeatureServer/0/query",
Query = [
where = "1=1",
resultOffset = "0",
resultRecordCount = "1000",
f = "json"
]
]
)This approach is recommended in various threads mentioned below. Follow this and let me know if it works for you — and feel free to support this post as a solution
https://stackoverflow.com/questions/77061776/this-dataset-includes-a-dynamic-data-source-since-dynamic-data-sources-aren-t
https://community.fabric.microsoft.com/t5/Desktop/Help-applying-Query-and-Relative-path-to-avoid-dynamic-data/td-p/1573538
Hi komyji ,
Thank you for reaching out to the Microsoft Community Forum.
Hi shashiPaul1570_ , Thank you for your prompt response.
Hi komyji , In addition to shashiPaul1570_ response, I am adding some more points.
Power BI Service requires a gateway when it cannot classify the data source as cloud-based. Even though your REST API is public and hosted online, Power BI sees the use of Web.Contents with dynamic parameters like pagination as a custom connector, which it treats as an on-premises source. This is due to how Power BI evaluates data privacy levels and query folding. When dynamic URL construction is involved, Power BI can't consider the source is safe to refresh without a gateway.
Can you refresh this REST API in Power BI Service without a gateway?
Solution: Yes, but with limitations and workarounds.
Please try below workarounds
1. Please try to move your M query into a Power BI Dataflow. Dataflows run in the cloud and often don't require a gateway for public web sources.
Go to Power BI Service --> Workspace --> Dataflows --> Create new. Use the same M code in the Power Query editor. Save and refresh the dataflow. Connect your report to the dataflow instead of the API directly.
3. Create an Azure Function or Logic App that handles the pagination and returns a single JSON response. Then connect Power BI to that endpoint. Power BI sees it as a single cloud source.
4. Minimize dynamic URL logic inside Power BI.
5. Preprocess data externally with Azure, AWS Lambda or a small web service.
6. Limit the number of pages if possible to reduce refresh time.
7. Cache data in a cloud database like Azure SQL and SharePoint list, connect Power BI to that.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi komyji ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @komyji ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @komyji ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh