how to create a query that paginates?

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.

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.

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.

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

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.

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

Ooops, I'm really sorry: The closing square bracket was at the wrong place. I've moved the steps around and it shifted to the wrong place:

//Previous code with access credentials 
let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=100&offset="&[Last_Key]&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
     			Last_Key = if [Counter]<=1 then "20170404130408053410572" 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

Works for me now, just expand the record (& ignore the error-message for a start): Transfer the list to a table & then you can expand the records you need.

Not sure about your other questions/aspects from your post: Is there anything that is still to be done now?

Pls check this code:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "20170404130408053410572", Counter=0], // Start Value
   		each  [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "20170404130408053410572" else [WebCall][lastKey] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://apiv2.clickmeter.com/datapoints/8697350/hits?timeframe=last30&limit=10&offset="&Last_Key&"%408693934&authKey=fde74f69-ea93-411f-96b2-5eb9cb4c0993")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"hits"}, {"hits"}),
    #"Expanded hits2" = Table.ExpandListColumn(#"Expanded Column3", "hits"),
    #"Expanded hits3" = Table.ExpandRecordColumn(#"Expanded hits2", "hits", {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"}, {"id", "accessTime", "entity", "browser", "os", "location", "conversions", "type", "ip", "isSpider", "isUnique", "trackedParameters"})
in
    #"Expanded hits3"

in your web call - in second part(else part) you are giving a variable inside web.contents - which is not supported in power bi service for scheduled refresh.

Thanks to excellent blog by Chris Webb - this can be solved - or lets say there is a way to overcome this issue.

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

In your case, you would need to prvide a fixed value here instead of "&Last_Key&" 

"https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset="&Last_Key&""))

 with query paramters as folow

Web.Contents("https://api.airtable.com/v0/ID/Audit?api_key=KEY&offset=someFixedValue",Query=[offset=Last_Key])

This someFixedValue has to be some valid value which works fine - say your first value of key - which will be used as a dummy value only to 'trick' the PBI service.

BR

emudria.

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)

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

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.

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"

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

That worked!!

YOU ARE AWESOME!!

Thank you so much ImkeF!!

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.

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

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

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

I'll give it a try later today.

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.

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.