I have a REST API source that requires paging, i.e. load the same URL with parameters ?page=1, ?page=2, ?page=3, … until empty result is returned.
I can use web connector and build these requests programmatically from a static list of (page) numbers, but this has two disadvantages that I need a solution for:
- The absolute killer for this solution is that you cannot schedule automatic refresh in Power BI service! It says, the source URL is built dynamically, so Power BI cannot determine for what data source to ask for authentication credentials (although not needed for my source) and security settings. The problem is desbribed here in section " Refresh and dynamic data sources": https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data
- Even a long static list of prosepective page numbers is not a really automated state of the art solution, but that’s the minor problem.
So, how do you connect to REST APIs that require calling an undefined number of pages to load a full table, if you need scheduled refresh in Power BI service? Isn’t there a build-in solution for this common scenario in Power BI?
For now I’ve built a dozen of queries manually - one query per page - to cover my data growth for a year or so, but that’s not a survivable solution.
Thanks a lot in advance!