cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpt1228
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: 

jpt1228_0-1617557231967.png

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"],

3 REPLIES 3
Eyelyn9
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.

jpt1228
Responsive Resident
Responsive Resident

Hello @Eyelyn9 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
Responsive Resident
Responsive Resident

Hello @Eyelyn9 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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.