Forum Discussion

gotmike's avatar
gotmike
Frequent Visitor
10 years ago

how to create a query that paginates?

I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the resu...
Need Help
ImkeF's avatar
ImkeF
Community Champion
9 years ago

Very much agree with Anonymous: You need to find out what your API can deliver. That's not a PBI-isssue.

You need to find a URL that contains a parameter that can be adjusted and "looped" through. We can help you with that once we see the syntax.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi ImkeF,

 

I have a similar issue to others in the thread.  I just cant seem to get my query to paginate.  I've done some initial exploring/research which will hopefully limit the amount of effort needed to solve this.  I'm very new to power BI and have little coding experience so any help would be appriciated.  Below is the code I've been playing around with (I've removed my token):

 

let
 iterations = 20,
 url = "https://az1.qualtrics.com/API/v3/mailinglists/ML_cwQxQJJ5adc1YyN/contacts", 
 FnGetOnePage =
   let
    Source = Json.Document(Web.Contents("https://az1.qualtrics.com/API/v3/mailinglists/ML_cwQxQJJ5adc1YyN/contacts", [Headers=[#"X-API-TOKEN"="my token here"]])),
    data = try Source[result][elements] otherwise null,
    next = try Source[result][nextPage] otherwise null,
    res = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

As you can see, I'm using an API with headers which may be the cause of some of the issues I'm having.  The url for the next page of data is in a field called "nextPage".  The recors are in a field called "elemts".  There are only 100 records per page and I have a few thousand records that I'd like to automatically bring in.  

 

Using the code above, I get the FnGetOnePage to run fine and it's producing the correct data in the "Data" and "Next" fields.  However, the List.Genreate function is where I'm getting an error.  Below is the error I'm getting:

 

Expression.Error: We cannot convert a value of type Record to type Function.
Details:
Value=Record
Type=Type

 

You seem to know what you're doing when it comes to this topic, I would love to hear your feedback or any suggestions you might have!

 

Thanks :)

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Yes, you're code is looking very good - especially for a "beginner" - kudos!

    I haven't changed much, pls see if the following code works for you:

     

    let
 iterations = 20,
 url = "https://az1.qualtrics.com/API/v3/mailinglists/ML_cwQxQJJ5adc1YyN/contacts", 

// Turn your query into a function where the url is fed in as a parameter
 FnGetOnePage = (url) =>
   let
   // Replace the hardcoded url to a reference to the parameter that's going to be fed in
    Source = Json.Document(Web.Contents(url, [Headers=[#"X-API-TOKEN"="my token here"]])),
    data = try Source[result][elements] otherwise null,
    next = try Source[result][nextPage] otherwise null,
    res = [Data=data, Next=next]
   in
    res,
 
 GeneratedList =
  List.Generate(
   ()=>[i=0, res = FnGetOnePage(url)],
   each [i]<iterations and [res][Data]<>null,
   each [i=[i]+1, res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    ImkeF, you are a legend!

     

    It worked, thank-you very much for the guidance (I knew I was close...)

     

    Thanks for the quick response as well, looking forward to working with my data now :)