how to create a query that paginates?

Hi sterling


I'm not an expert at this and I had a lot of help writing the query (thanks again ImkeF!), but could the API you're using limit you to a certain amount of results? Is there a "HAS MORE" (pages) with a "TRUE" or "FALSE" option for example? I don't see a page option in your URL, but if that can be included, below is the solution that ImkeF sent me, and it resolved my issue. Notice: p="&Text.From([Column1])&"

let
    Source = {1..11},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Web.Page(Web.Contents("http://www.boerse-online.de/index/liste/S&P_500?p="&Text.From([Column1])&"")){0}[Data])
in
    #"Added Custom"

I wish I had more specific technical advise here, but hopefully that helps.

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 :)

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

Hello Anonymous

I`m also working to get the results from Hubspot Deals. Do you mind sharing your query?

Hi remix

Apologies for the delay. My environment is unique, and the database wound up being to big to refresh, but here's essentially what I was working with on my tests:

let

Source = {1..100},

Source1 = ({"abcd", "abcde"}),
    #"Converted to Table1" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Added Custom1" = Table.AddColumn(#"Converted to Table1", "Custom", each Json.Document(Web.Contents("https://test.com/leads?campaign_id="&Text.From([Column1])&"&start=2016-01-01&api_key=123"))[items]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://test.com/leads?campaign_id=abcd&start=2016-01-01&api_key=123&page="&Text.From([Column1])&""))[items]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"sale_price", "good"})
in
    #"Expanded Custom1"

I have a similar question. Below is my query but Airtable is only returning 100 records. Thist list will be growing so I want it to fetch all records for this query (that it's limiting to 100 right now).

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/123456/TABLE?api_key=APIKEY")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "fields", "createdTime"}, {"id", "fields", "createdTime"}),
    #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"ML#", "Curr Price", "Status", "Address", "Subd/Complex", "Area", "Age Desc", "City", "Zip Code", "Style", "Association Fee", "County", "Total Bedrooms", "Total Full Baths", "Total Half Baths", "Year Built", "Photo Count", "List Date", "Square Footage", "Total Days on Market", "Current Price/SQFT", "Original List Price", "List Price", "SqFt Source", "Type", "Expiration Date", "Approximate Lot Size", "Latitude", "Longitude", "Accuracy Score", "Accuracy Type", "Number", "Street", "City 2", "State", "County 2", "Zip", "Country", "Binding Agreement Date", "Sales Price", "SP/OLP", "Closing Date", "Costs Paid by Seller"}, {"ML#", "Curr Price", "Status", "Address", "Subd/Complex", "Area", "Age Desc", "City", "Zip Code", "Style", "Association Fee", "County", "Total Bedrooms", "Total Full Baths", "Total Half Baths", "Year Built", "Photo Count", "List Date", "Square Footage", "Total Days on Market", "Current Price/SQFT", "Original List Price", "List Price", "SqFt Source", "Type", "Expiration Date", "Approximate Lot Size", "Latitude", "Longitude", "Accuracy Score", "Accuracy Type", "Number", "Street", "City 2", "State", "County 2", "Zip", "Country", "Binding Agreement Date", "Sales Price", "SP/OLP", "Closing Date", "Costs Paid by Seller"})
in
    #"Expanded fields"

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.

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 :)