Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gotmike
Frequent Visitor

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 results.

 

For instance, if you send:

https://api.hubapi.com/deals/v1/deal/all?hapikey=demo

 

at the very end of the query, you see the following JSON:

 

"hasMore":false
"offset":27939158

so, if hasMore is true, the NEXT query should look like this:

https://api.hubapi.com/deals/v1/deal/all?hapikey=demo&offset=27939158

 

and then, we would want to repeat the process until hasMore comes back with false.

 

i'm completely new to power bi, so would love to know how to handle this type of query process.

 

in another language, this would just be do { } while (hasMore == false);

or something like that...

214 REPLIES 214

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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 🙂

Pls check this out:

 

let

Source = Json.Document(Web.Contents("api.mywebsite.com/leads?myapikey=1234567")),
Source1 = {1..Source[page_count]},
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("api.mywebsite.com/leads?myapikey=1234567&page="&Text.From([Column1])&"")))
in
#"Added Custom"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

I'm thinking I might be able use something like:

 

if Source[has_more]="TRUE" then go to next page

 

I just don't know M language to figure this out on my own...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.