PowerBi relative path pagination

[Tinus1905]

Hi,

I have a little problem with my dataset refreshing in PowerBi service. 

My dataset is from an API with pagination. Below my link:

Bron = Json.Document(Web.Contents("https://app.website.nl/api/company/v1/account/sales/export/default-extended?page="&Number.ToText(page), [Headers=[Accept="application/json"]])),

When I search the internet, I have to use a relativePath? How do I change the link with relativePath so PowerBi will refresh again. 

Or should I try something else?

Thanks.

[Tinus1905]

Yes, thanks. 

Query = [page = Number.ToText(page)], now I get all the pages. 

[MarkLaf]

Check out these posts from Chris Webb for a nice explanation: 

1. https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
2. https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

When using Web.Contents, you generally want to split up something like:

Root API path	Root relative path	Query parameters
https://app.website.nl/api/company/v1/	account/sales/export/default-extended	?page='10'&prop=5

into:

Web.Contents(
	"https://app.website.nl/api/company/v1",
	[
		RelativePath = "account/sales/export/default-extended",
		Headers = [ accept="application/json" ],
		Query = [
			page = "10",
			prop = 5
		]
	]
)

If you are unsure where to split root/relative, just test your endpoint up the path until you find the highest level where you still get a valid response. Depending on API, you can just test quickly in your browser.

For performing multiple page calls, you'll want to do something similar to #2 link above.

E.g. (example where we get pages 1-10 and using Web.Contents directly without wrapping in a function like in linked example)

List.Generate( 
	() => 1, each _ <= 10, each _ + 1, //go from 1 to 10
	each 
	Json.Document( Web.Contents(
		"https://app.website.nl/api/company/v1",
		[
			RelativePath = "account/sales/export/default-extended",
			Headers = [ accept="application/json" ],
			Query = [
				page = Text.From( _ ) //using value created by List.Generate
			]
		]
	) )
)

[Tinus1905]

The example where you got the first 10 page works, but how do I get all the pages instead of 10? 

Im not sure how many pages there are. 

[MarkLaf]

Check the API documentation. Is there a way to see how many pages or total items there are? If yes, you can then figure out how to dynamically construct the input list of pages ahead of time, which should run faster.

The alternative is the method you are trying in your other post, where you make the web calls until you get a bad response. You are on the right track and overall looks good.

I think the one problem you are running into is that your Query record in fSales does not look correct:

What you have now:
Query = [page = "&Number.ToText(page)"]

What it should be:
Query = [page = Number.ToText(page)]

[Tinus1905]

ok, I now know where to split the path. This is what I got so far. 

First table:

(page as number)=>
let
Bron = Json.Document(Web.Contents("https://app.website.nl/api/company/v1/account/sales",
[RelativePath = "export/default-extended",
Query = [page = "&Number.ToText(page)"],
Headers = [ accept="application/json" ]])),

Second table:

let
Bron = List.Generate(()=>
[result = try fSales(1) otherwise null, page=1],
each [result]<>null,
each [result = try fSales([page]+1) otherwise null, page=[page]+1],
each [result]),

So far so good. But only got the first page over and over again instead first page + 1, +2 etc. 

What am I doing wrong?

[lbendlin · Accepted solution]

Query = [page = Number.ToText(page)],

[lbendlin]

Example 1

Web.Contents - PowerQuery M | Microsoft Learn