Reply
Frequent Visitor
Posts: 13
Registered: ‎02-27-2017

Re: how to create a query that paginates?

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

 

im1.PNG

Super Contributor
Posts: 1,020
Registered: ‎09-06-2015

Re: how to create a query that paginates?

[ Edited ]

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

Frequent Visitor
Posts: 13
Registered: ‎02-27-2017

Re: how to create a query that paginates?

Imke, thank you for the quick response. It's super helpful.

I'll give it a try later today.

Frequent Visitor
Posts: 13
Registered: ‎02-27-2017

Re: how to create a query that paginates?

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:

Capture2.PNG

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.

Highlighted
Super Contributor
Posts: 1,020
Registered: ‎09-06-2015

Re: how to create a query that paginates?

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.

 

Super Contributor
Posts: 1,020
Registered: ‎09-06-2015

Re: how to create a query that paginates?

[ Edited ]

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

 

 

Frequent Visitor
Posts: 13
Registered: ‎02-27-2017

Re: how to create a query that paginates?

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!

 

Capture3.PNG

Super Contributor
Posts: 1,020
Registered: ‎09-06-2015

Re: how to create a query that paginates?

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
Frequent Visitor
Posts: 13
Registered: ‎02-27-2017

Re: how to create a query that paginates?

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

 

Super Contributor
Posts: 1,020
Registered: ‎09-06-2015

Re: how to create a query that paginates?

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.