Forum Discussion
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 http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/comment-page-1/?unapproved=263088&moderation-hash=bb248decf916084bb62b4088f68426d3#comment-263088
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.
my query in the advanced editor:
let
Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Headers=[AUTHORIZATION="Basic xxxxxxxxxxxxxxxxxxxxxx="]])),
Query=[page="(pagestart)&per_page=500"],
3 Replies
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
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 Anonymous Thanks for your reply. If I put all this together in one query is this the correct query string?
(Page as text) =>
let
Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Headers=[AUTHORIZATION="Basic xxxxxxxxxxxxxxxxxxxxxx="]])),pageRange = {0..Number.RoundUp(totalItems / 250)-1}
pages = List.Transform(pageRange, each Page(_*250+1)),Hello Anonymous 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.
Thanks
