Dynamic M Query

[Dai5ho]

Hello all,

I am trying to pull a json file via a WEP API. Because of the source settings (not to change since external), I need to paginate my query to not get more than 5000 records per query.
In total there are more than 35000 records, I would have to run this query 8 times accordingly to get all records and append each.
Since the number of records is continuously increasing, the number of queries should increase accordingly.
The total number is to be queried. Likewise also easily the number of the queries.
Is there a way to program a dynamic query that repeats and appends the same query corresponding to the parameter?

let

Quelle = Json.Document(Web.Contents("https://xxx.com/api/v1/bookings?per_page=5000&page=1", [Headers=[Accept="application/json", #"X-ApiKey"="xxx", Authorization="Basic xxx"]])),
data = Quelle[data],

in
data

"1" has to be dynamic. I already have a list of the pages. But do not know how to insert the values into the query...

Thank you in advance for your support.

[AlB]

Hi @Dai5ho 

Just create a variable with the dynamic part and concatenate it with the base string. Then use that variable as first argument of the Web.Contents function. Something like:

let
var_ = "1"
Quelle = Json.Document(Web.Contents("https://xxx.com/api/v1/bookings?per_page=5000&page=" & var_, [Headers=[Accept="application/json", #"X-ApiKey"="xxx", Authorization="Basic xxx"]])),
data = Quelle[data],

in
data

You can then build the iterative code to update var_ with the required number page and use it within Json.Document to pull the next piece of data 

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Dai5ho]

Hi @AlB ,

thx for your fast answer. 

yes this works with a fixed variable that always contains a record e.g. "1".
But with more than 35000 total records I have to run this query 8 times and append the result again and again to the others, because the parameter contains a list of "1...8".

means the part

"...per_page=5000&page=" & var_"

must change accordingly dynamically into several queries

page=1
page=2
page=3
... (after that sth like table.combine)

So that I get the total records of the source through this. This should also automatically adjust to the total number of records.

I have the total number of a query and have created a dynamic list of page numbers through this....

Just how to incorporate this list into the actual query accordingly, so that in principle by a programmed function query n queries arise.

Or am I wrong?