Scheduling dynamic anonymous API Refresh in Power BI service

[jpt1228]

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... 

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:

let
Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?", [Headers=[AUTHORIZATION="Basic xxxxxxxxxxxxxxxxxxxxxx="]])),
Query=[page="(pagestart)&per_page=500"],

[v-eqin-msft]

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.

[jpt1228]

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) =>

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)),

[jpt1228]

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.

Thanks