how to create a query that paginates?

[gotmike]

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

[kroll]

Hi,

Have you found a solution to your problem?
I'm running into the same issue...

Thank you in advance!

[Anonymous]

FYI: I've tried this below, and I'm still only getting 50 records as opposed to 96209... Any thoughts?

let

iterations = 10,
url = "https://mywebiste.com/leads?api_key=myapikey",

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[items] otherwise null,
next = try Source[page_count][has_more] 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]),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")
in
#"Expanded Column1"

[ImkeF]

It looks as if you're missing the {0} when trying to select the values.

Try debugging your function by using placeholders to see what kind of values each of your step actually returns (a bit like here: http://www.thebiccountant.com/2016/05/30/analyze-m-functions-step-step/ )

[Anonymous]

Thanks ImkeF,

I've tried copying/pasting around the function, with no success.

I did notice that when I copy/paste the URL after the "FnGetOnePage", I get a lot of lists and tons of columns, but they're all blanks...

I have to admit, I'm in a little over my head on this one, but I know I'm close.

Because I copied this code from a how-to for getting data from Facebook, I think it might have to do with what's different on this website/API. I've tried to edit the code to fit this situation as much as possible, but maybe it's off somehow.

Here are the results I get from the website I'm getting data from, before breaking anything out into a table:

Here is my code to try and work with all of the 96209 records. Again, I only get 1List and 50 records when I expand out the list:

let

iterations = 200,
url = "https://api.thewebsite.com?api_key=myapikey",

FnGetOnePage =
(url) as record =>

let

Source = Json.Document(Web.Contents(url)),
data = try Source[items] otherwise null,
next = try Source[has_more][next] 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]),

#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Any thoughts?

[ImkeF]

Could You please send the link where you've copied the code from?

[Anonymous]

Sure. Here's the blog:

http://datachant.com/2016/06/27/cursor-based-pagination-power-query/

FYI: What I'm actually connecting to through the Power BI "web" source is an API. I'm not sure if that changes anything.

[ImkeF]

That article describes the procedure for a cursor-based pagination, which means that the URL for the next step will be returned from its previous step.

In the screenshots you've provided I cannot spot such a field. Could it be that your source paginates differently, for example just by counting pages?

Then you would need to use a different method.

For cursor-based-pagination you need to explore the first record (or table) returned from the first step and try to find the field that contains the field with the key for the next iteration.

Thats what goes into the step "next".

[Anonymous]

Thank you ImkeF,

I really appreciate you taking the time to help me with this.

That must be it.

I also just realized that in the data I get back from this source, there is a "has_more" row that contains "TRUE" if there are more pages and "FALSE" if there are no more pages. It also shows the "page_count". I can even tell it what page I want in the URL by including the paramater "page=x".

If that's the case, I'm thinking the query/code would definitely need to be different, and possibly more straightforward?

Any advise on how that would look? (screen shot of the response back below)

[ImkeF]

No need for recursion here.

This is an easy example:

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"

You need to modify it like this:

1) Step Source: replace 11 by your page_count (YourRecord[page_count])

2) Step Added Custom: Replace by your url and replace the page number by: "&Text.From([Column1])&"

This should return the correct record per page which you can then further expand.

It creates a list of your pages, turns it into a table and then adds a column where each page is called by its individual URL.

[Anonymous]

We're so close I can taste it...

So, if I use just what you sent for my scenario and change it to my environment (URL, page, etc), the wheels just keep turning and nothing happens.

So I edited it a little bit and almost got something, but I'm getting an error: "Expression.Error: We cannot convert a value of type Record to type List.
Details:
Value=Record
Type=Type

Here's a screen shot of where it almost seems to work...

You'll have to excuse my lack of knowledge on this...

Here is the full code I used... (FYI: I'm contecting to an Json.Document not actually a Web.page)

let

Source = Json.Document(Web.Contents("api.mywebsite.com/leads?myapikey=1234567")),
Source1 = {1..11},
#"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])&"")){0}[Data]),
Custom = #"Added Custom"{0}[Custom]
in
Custom

Any thoughts?

Thanks again for all your help.

[Anonymous]

FYI: The other finally went through and I'm getting the same ("We cannot convert a vlue of type Record to type List") error.

Here's the code:

let

Source = {1..200},
#"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://api.mywebsite.com?myapikey=1234567&page="&Text.From([Column1])&"")){0}[items]),
Custom = #"Added Custom"{0}[Custom]
in
Custom

[ImkeF]

Delete the {0}, so just:

let

Source = {1..200},
#"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://api.mywebsite.com?myapikey=1234567&page="&Text.From([Column1])&""))[items])
in

#"Added Custom" 

Then check what format is returned in column [Custom] before deciding on how to expand that.

[kroll]

Hi Imke,
Since you are an expert, I hope you can recommend a solution (or resources) to the following problem:

I want to leverage REST API and run something like:
https://URL.com?fromDay=20170301&toDay=20171231&offset=20170319015902380428278%4041627&authKey=123

so the query is:

let
Source = Json.Document(Web.Contents("https://URL.com?fromDay=20170301&toDay=20171231&offset=20170319015902380428278%4041627&authKey=123")),
#"Converted to Table" = Record.ToTable(Source),

...

The response from the server provides json with 100 records and a "lastKey" number that should be used to pull the next 100 records (by using "offset" parameter). It looks like:
{
"lastKey": "20170319015902380428278",
"hits": [
{
...
}

(screenshot below show that in PowerBI)

The next URL should be:
https://URL.com?fromDay=20170301&toDay=20171231&offset=20170319015902380428278%4041627&authKey=123
(where "%4041627" in the offset parameter is a fixed value)

When the last page is reached, "lastKey" disappears from the response.

Question - how can I automate the process of pulling the data?

When I run the first URL, I get the output below, and have no idea where to go from here.

I will appreciate your guidance.
Thank you in advance,
Peter

[ImkeF]

Hi Peter,

This is a "real" pagination and I think List.Generate is best to handle this. The code would probably be look like so:

let
Pagination = List.Generate( () => [Last_Key = "20170319015902380428278"], // Start Value
			each 	[Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
			each	[Result = "https://url.com/?fromday=20170301&today=20171231&offset="&[Last_Key]&"%4041627&authKey=123", // retrieve results per call
			 	 Last_Key = Result[lastKey]	], // determine the LastKey for the next execution
			each	[Result]) // Select just the Result-record
in
Pagination

It might need a bit of tweaking because I couldn't test it, but the general principle is this:

1) pass the necessary parameters into the first argument of the function (here: Start value for LastKey

2) define the condition under which the execution of the next step shall happen

3) define the record which contains the step(s) to execute

4) optional argument which lets you select specific record-fields: In this case we're just interested in the "Result" and not the LastKeys used

[kroll]

Hi Imke, I've tried... but I need a little more guidance.

I followed the post on Chris Webb's BI Blog, but I need to learn some basics to leverage that knownledeg (e.g. how do I post a value from the external table into my query?).

I have two questions:

1. I've tried the code, and I got the following error:

2. Do I have to combine (nest?) the pagination code with the code I alredy have?

let
    Source = Json.Document(Web.Contents("https://url.com/?fromday=20170301&today=20171231&offset=20170320170427721959426%4041627&authKey=123")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
...
in
    #"Expanded Column1"

Thank you in advance for your help.

[ImkeF]

Hm, yes, there's an issue with the first item in the list - will check that. What does Pagination{1} deliver?

I wouldn't recommend to include your other code in there. Instead I'd transform the returned list into a table and add a custom column where you execute your other code (as a function) on a row-level.

[ImkeF]

I modified it a bit and included your further transformation steps in there as well - might actually be the best idea because it will prevent multiple API-calls (hopefully...). Just make sure that in the last "each-step", you reference the last step of your transformations (where I've now replaced "Result" with "Value":

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170319015902380428278", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [	WebCall = "https://url.com/?fromday=20170301&today=20171231&offset="&[Last_Key]&"%4041627&authKey=123", // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170319015902380428278" else WebCall[lastKey] ],// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value], // last step of your further queries
   		each [Value]),1) // Select just the Record of the last step from your query
in
Pagination

[kroll]

Imke, thank you for the updated code. Unfortunately, I got the error below.  Since you are not able to test this code, I'm not surprised that there could be some issues.

My understanding of the code was (still is) relatively limited, so I spend the evening going through some of the learning resources listed on your website. They are super useful, by the way. However, I did not have a chance to try to fix the error (most likely, I would not be able to do it anyway...).

If you have any sugestions, I'll be happy to test them.

Thanks!

[ImkeF]

Thx Peter for the kind feedback.

Did you adjust the 1st step accordingly (in comparison to the 1st version)?:

Pagination = List.Skip(List.Generate( () => [Last_Key = "20170319015902380428278", Counter=0], // Start Value

[kroll]

Yes, I've doubled checked the code.

From my very limited "testing"... is it possible that the bolded part generates the error? Is there something missing?

Thank you in advance!

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170319015902380428278", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [	WebCall = "https://url.com/?fromday=20170301&today=20171231&offset="&[Last_Key]&"%4041627&authKey=123", // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170319015902380428278" else WebCall[lastKey] ],// determine the LastKey for the next execution
     			Counter = [Counter]+1,// internal counter
     			#"Converted to Table" = Record.ToTable(WebCall), // steps of your further query
     			Value = #"Converted to Table"{1}[Value], // last step of your further queries
   		each [Value]),1) // Select just the Record of the last step from your query
in
Pagination

[ImkeF]

Yes, I can understand you assesment, but this shouldn't be the cause. Pls check the following query that paginates through 3 webpages using this method successfully:

let
Pagination = List.Skip(List.Generate( () => [Result = Web.Page(Web.Contents("http://www.finanzen.net/aktien/US-Aktien-Realtimekurse@intpagenr_"&Text.From(Counter)))[Data]{0}, Counter = 0], // Start Value
			each 	[Counter] <=3, // Condition under which the next execution will happen
			each	[Result = Web.Page(Web.Contents("http://www.finanzen.net/aktien/US-Aktien-Realtimekurse@intpagenr_"&Text.From(Counter)))[Data]{0}, // retrieve results per call   
			        Counter = [Counter]+1	], // determine the LastKey for the next execution
			each	[Result]
),1),
    Combine = Table.Combine(Pagination)
in
    Combine

If you find a website where we could harvest  the "next page" in the results returned, pls forward and we can test your scenario there.