Dynamic value in web source API URL based on table value

[SamTOG]

Hello

I am using an API to get booking data from Bookeo.com. Bookeo limits queries to max 100 records per page of results (see more here: https://www.bookeo.com/api/protocol/). Most months we receive more than this, so I create a new query for each page of results, using their protocal: "To navigate to another page, an application can simply invoke the same API endpoint again, passing only two parameters, pageNavigationToken and pageNumber".

E.g. https://api.bookeo.com/v2/…?pageNavigationToken=xDgBr3m8qxLWtkSA&pageNumber=5

The problem is, everytime I refresh the original query, the pageNavigationToken changes, which causes the query retrieving the 2nd page of results to fail (because it is still using the previous pageNavigationToken)

What I want is for the pageNavigationToken in the 2nd page's web source URL to reference the value in the first table of results. This means when the value changes, the URL connection for the 2nd page of results automatically updates with the latest pageNavigationToken.

Is there a way to reference a value from a table in a web source URL?

E.g. https://api.bookeo.com/v2/…?pageNavigationToken= (formula to retrieve latest top value from (Table[pageNavigationToken])&pageNumber=5

Thanks!

[SamTOG · Accepted solution]

Hi everyone who's interested. I solve the problem myself:

1. I created a query that just returns the pageNavigationToken as a value. This changes at every refresh.

2. I created queries that reference this pageNavigationToken in their connection URL:

https://api.bookeo.com/v2/bookings?secretKey=*****&apiKey=*****&pageNavigationToken="&#"pageNavigationToken" &"&pageNumber=1

Previously, this would return the Formula.Firewall error. The way I got around this was to go to: File -> Options and settings -> Options -> Privacy, then select "Always ignore Privacy Level settings". This does come with a warning about exposing data to unauthorised persons. However, it lets me connect to the external source at the same time as referencing a different query.

Now, when the pageNavigationToken changes with every refresh, so does the dependent queries' connection URLs.

Thanks for listening 🙂

[SamTOG]

As a test, I entered this:

= Json.Document(Web.Contents("https://api.bookeo.com/v2/bookings?secretKey=*****&apiKey=*****&pageNavigationToken=" & Table.FirstValue(#"Bookings 2022-04 Pg1") & "&pageNumber=2"))

It returned the error:

"Formula.Firewall: Query 'Bookings 2022-04 Pg2 (2)' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."