Showing results for 
Search instead for 
Did you mean: 
Responsive Resident
Responsive Resident

Scheduling dynamic anonymous API Refresh in Power BI service

Hello, I have an API call which I can refresh in the desktop fine. When I publish to the service I am unable to schedule refresh due to error: 


I have been successful using 


to modify my query and use anonymous credentials but the query only returns 1 page x 100 rows. When it iterates on the pagenumber function it just repeats the same 100 rows. For example 1 page x 100 rows = count=100 and distinct count = 100. When it iterates on 5 pages I get count=500 distinct count=100.  I would expect 5 pages would return count=500 and distinct count=500.


I have looked at the below post but cannot figure it out for my specific instance.

Dynamic Web Contents Error 


my query in the advanced editor:


Source = Json.Document(Web.Contents("", [Headers=[AUTHORIZATION="Basic xxxxxxxxxxxxxxxxxxxxxx="]])),

Community Support
Community Support

Hi @jpt1228 ,


Try this:

pageRange = {0..Number.RoundUp(totalItems / 250)-1}
pages = List.Transform(pageRange, each ufnCallAPI(_*250+1)),

You can take a look at the following document about the help functions about processing pagination api if it meet your requirement:

Helper functions for M extensions for Power Query connectors | Microsoft Docs

Solved: Power Query M function in Paginate API - Microsoft Power BI Community

Solved: Paginate Rest API via Offset and Limit method - Microsoft Power BI Community

Iterate over dynamic Web API Pages with Power Query - How to resolve Cursor-Based Pagination - DataC...

Revisiting Dynamic Web.Contents()


Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-eqin-msft Thanks for your reply. If I put all this together in one query is this the correct query string?


(Page as text) =>

Source = Json.Document(Web.Contents("", [Headers=[AUTHORIZATION="Basic xxxxxxxxxxxxxxxxxxxxxx="]])),

pageRange = {0..Number.RoundUp(totalItems / 250)-1}
pages = List.Transform(pageRange, each Page(_*250+1)),


Responsive Resident
Responsive Resident

Hello @v-eqin-msft I wanted to follow up on your response. Where do I put your suggested modifications? Does it replace the "Query="? If you could help with the complete API call it would be much appreciated.



Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors