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.

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

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"

Have you refreshed the query? I've used a filter which I might have forgotten to take out.

For learning purposes I'd suggest that you step through the queries: Pretty sure you find the filters (just delete them) and the answer to your URL question in there ;)

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.

Hi,

I'm trying to understand this code and manipulate it for my own query, but I'm having difficulty. Do you have any thoughts on how I would edit this in order to display all pages on this query?

I've attached a screen shot of what I see when I first bring the query in. There are 1132 pages and 56580 records, but when I expand it out, I only get 50 records.

Screen shot above and here is the final code below (the URL is bogus for security purposes):

let
Source = Json.Document(Web.Contents("https://api.thewebsite.com/stats/leads?&start=2016-01-01&end=2016-11-25api_key=myapikey")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{2}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"bad", "campaign_id", "campaign_name", "converted", "created_at", "delivered", "delivery_summary_id", "error_delivery_summary_id", "good", "id", "invalid", "margin", "marked_for_wipe_at", "pickled_at", "profile_id", "profit", "purchase_price", "recipient_id", "rejected", "returned", "sale_price", "source_id", "source_lead_id", "values", "wiped_at"}, {"bad", "campaign_id", "campaign_name", "converted", "created_at", "delivered", "delivery_summary_id", "error_delivery_summary_id", "good", "id", "invalid", "margin", "marked_for_wipe_at", "pickled_at", "profile_id", "profit", "purchase_price", "recipient_id", "rejected", "returned", "sale_price", "source_id", "source_lead_id", "values", "wiped_at"})
in
#"Expanded Column1"

Thanks for any advise!!

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"

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

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?

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.

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?