Rest API _ Json _ several pages _ automatically call the next_page_URL

you're right - the url need to be called somewhere as well :)

so how does this work?:

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    Sample =  #"Expanded Column1" {230}[res]
in
   Sample

Very pleased to hear Anonymous ,

you might leave a kudos then ;)

Thx!

ImkeF 

Thanks to this thread I was able to replicate a lot and I am very close to finally solve my particular challenge as well :) 

The only problem I have: I don't know the number of total records, hence my iteration has to run until the return [next_page] from the API runs into a Null.

My code works, but it seems to be an infinite loop and what I get is a List of Lists (a list of pages), that I need to expand to a full table.

let
 Source = Json.Document(Web.Contents("https://myURL")),
   tickets = Source[tickets],
   url = "https://myURL", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page 
    res = [Data=data, Next=next]
   in
    res,

 GeneratedList =
  List.Generate(
   ()=>[res = FnGetOnePage(url)],
   each Source[next_page]<> null,
   each [res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

Can you help me? I think I am very close... 

Thanks in advance!

Hi Anonymous ,

The 2nd argument in the List.Generate can not do its job, as it references the original table. (Actually it returns the column "next_page" from the original table. As that is never null, you have indeed an infinite loop here.

You have to reference something from the previous step instead. This can be done by using the stepname as a lookup like so:

let
Source = Json.Document(Web.Contents("https://myURL")),
   tickets = Source[tickets],
   url = "https://myURL", // I am using basic authentication

FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page 
    res = [Data=data, Next=next]
   in
    res,

GeneratedList =
  List.Generate(
   ()=>[res = FnGetOnePage(url)],
   each [res][Next]<> null,
   each [res = FnGetOnePage([res][Next])],
   each [res][Data])
in
    GeneratedList

[res][Next] returns the value from the latest step, so should return what you need.

BTW: When developing List.Generate-applications, I'm always using an additional condition during develpment phase, to avoid becoming trapped in an infinite loop like so:

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next]<> null and [Counter] < 5, 
		each [res = FnGetOnePage([res][Next])
			Counter = [Counter]+1	], 
		each [res][Data]) 

Hi ImkeF 

Ah, I see my mistake! Makes sense thank you! Thanks a lot for the swift reply!

Every row in the GeneratedList is a Page (which it should be according to the code) each containing 100 rows... When run thorugh the pagination manually I get an empty NextPage on page 236, so this is the last page with data.... but the GeneratedList goes beyond 236. This can't be correct right?

Finally, how do I get my list of lists converted into a table, as the response is on JSON?

Thanks again!

Maybe its a different field then, or it shows something else than null.

Explore the actual results by omitting the 4th element of List.Generate, that narrows down the returned result like so:

GeneratedList =
  List.Generate(
   ()=>[res = FnGetOnePage(url)],
   each [res][Next]<> null,
   each [res = FnGetOnePage([res][Next])]
  // , each [res][Data])
in
    GeneratedList

Click on the field and check what's actually going on.

You should be able to expand the list with the UI. Transform to table and click your way through.

Hi Imke

So I tried to figure out what happens. Even when omitting the statement as you described, it is an infinity loop.

I think it might have to do with the fact that we don't use the variable "data" that was defined FnGetOnePage? In the loop, it is never put to action. Can this be the cause?
I also checked the response of NextPage and indeed it is null, as shown in the picture below when querying page 236 directly:

Using the counter in the code has shown that it only stops at the end of the counter I put in (e.g. 1000 will just produce rows until 1000, 200 will make it stop at 200). So another proof that it loops forever...The current code I have is as follows:

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https://myURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next]<> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]) 
		//each [res][Data]) 
in
    GeneratedList

Hm, that's a bit strange.

What do you get if you expand the 235th result of the invinite loop?

ImkeF 

I realized something today! When I extend the counter to 1000 and look at the expanded tables in detail, I saw that it actually loads the first page 1000 times. I filtered for the ticket ID (which is unique for every member in Source[tickets]) and when I count rows, I always get the same number as my counter... So it seems that the code actually does not jump to the next page, but is stuck in loading the first page over and over again.

But I don't see why it won't go beyond the first page. The Source[next_page] is correct, and I tried rewriting the code in many ways...

Do you have an idea?
Could the reason be the fact that Source[tickets] returns an actual list whereas Source[next_page] only provides the URL? 

let
  url = "https://myURL/api/v2/tickets.json", // I am using basic authentication
  Source = Json.Document(Web.Contents(url)),
  Tickets = Source[tickets],
  NextPage = Source[next_page],
   
 FnGetOnePage =
  (url) as record =>
    let
    data = try Tickets otherwise null, //get the data of the first page
    next = try NextPage otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1]	)
in
    GeneratedList

I cannot see why this is could be the reason.

Coming back ot my previous post, I'd like to see what is actually returned (on each level, if a nested object is returned).

ImkeF 

Okay, so the GeneratedList Step looks as follows:

When expanding the list to see the full records, it looks like this:

And then when I filter for one ID, what I get is the same ID 237 times (as the counter is set to stop at 237):

And this is the full code (up until the step when I filter for ID):

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    #"Expanded res" = Table.ExpandRecordColumn(#"Expanded Column1", "res", {"Data"}, {"res.Data"}),
    #"Expanded res.Data" = Table.ExpandListColumn(#"Expanded res", "res.Data"),
    #"Expanded res.Data1" = Table.ExpandRecordColumn(#"Expanded res.Data", "res.Data", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"Column1.res.Data.url", "Column1.res.Data.id", "Column1.res.Data.external_id", "Column1.res.Data.via", "Column1.res.Data.created_at", "Column1.res.Data.updated_at", "Column1.res.Data.type", "Column1.res.Data.subject", "Column1.res.Data.raw_subject", "Column1.res.Data.description", "Column1.res.Data.priority", "Column1.res.Data.status", "Column1.res.Data.recipient", "Column1.res.Data.requester_id", "Column1.res.Data.submitter_id", "Column1.res.Data.assignee_id", "Column1.res.Data.organization_id", "Column1.res.Data.group_id", "Column1.res.Data.collaborator_ids", "Column1.res.Data.follower_ids", "Column1.res.Data.email_cc_ids", "Column1.res.Data.forum_topic_id", "Column1.res.Data.problem_id", "Column1.res.Data.has_incidents", "Column1.res.Data.is_public", "Column1.res.Data.due_at", "Column1.res.Data.tags", "Column1.res.Data.custom_fields", "Column1.res.Data.satisfaction_rating", "Column1.res.Data.sharing_agreement_ids", "Column1.res.Data.fields", "Column1.res.Data.followup_ids", "Column1.res.Data.brand_id", "Column1.res.Data.allow_channelback", "Column1.res.Data.allow_attachments"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded res.Data1", each ([Column1.res.Data.id] = 103733)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Column1.res.Data.id] = 103733))
in
    #"Filtered Rows"

I seriously lost here... much appreciate your support here!

Thanks again!

thanks Anonymous  

but I need to see the following steps:

•  Expanded Column1
•  Expanded res
•  Expanded res.Data

ImkeF Alright here you go:

Expanded Column1

Expanded res

Expanded res.Data

Thanks!!

Thanks, but I still need more, unfortunately: What does this return?:

let
 Source = Json.Document(Web.Contents("https://MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "https:/MYURL/api/v2/tickets.json", // I am using basic authentication
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Source,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 237, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1	]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res", "Counter"}, {"res", "Counter"}),
    Sample =  #"Expanded Column1" {230}[res]
in
   Sample

ImkeF Thanks for the prompt reply, sure, Here you go:

This is the step "Example"

And this is what happens when I work my way thorugh.

Hi Anonymous ,

you're not using the function parameter in the fnGetOnePage-function.

Change step Source like so:

Source = url,

instead of: Source = Source,

(so it just calls the first query over and over again..)

... I know, it hurts ;)

Hi ImkeF 

Really? that can't be. Because when I change it in the code to this:

FnGetOnePage =
  (url) as record =>
   let
    Source= url,
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1])

in
    GeneratedList

It just returns an empty list in the step GeneratedList:

If I omit the definition of Source in the FnGetOnePage step entirely, what I get is the same result as before with the loop being stuck on one page. And really, since the source does not change for the FnGetOnePage, is it needed to define this parameter?
It should always start on the /MYURL/tickets.json URL, but grab data (the current page, tickets) and next (the NextPage) depending on the return of "NextPage".

Could it be that we need to overwrite the url in each iteration to grab the latest url from the latest step? Right now it is pointing to page 2 over and over again, right? so it's stuck on the next_page reference for page one (the MYURL/tickets.json default response) for every step in the loop...
So I think the problem might be here:

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1])

But I don't have enough experience with M to really see what's going on...

Again, I think I am very close but I just don't seem to get it right...:)

ImkeF Success!! Finally, it worked. So here's the final overall code, including the steps to get to the final full table:

let
 Source = Json.Document(Web.Contents("MYURL/api/v2/tickets.json")),
   tickets = Source[tickets],
   url = "MYURL/api/v2/tickets.json", // I am using basic authentication
 
  FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url)),
    data = try Source[tickets] otherwise null, //get the data of the first page
    next = try Source[next_page] otherwise null, // the script ask if there is another page
    res = [Data=data, Next=next]
   in
    res,

GeneratedList = 
	List.Generate( 
		()=>[res = FnGetOnePage(url), Counter = 0], 
		each [res][Next] <> null and [Counter] < 1000, 
		each [res = FnGetOnePage([res][Next]),
			Counter = [Counter]+1]),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"res"}, {"res"}),
    #"Expanded res" = Table.ExpandRecordColumn(#"Expanded Column1", "res", {"Data", "Next"}, {"Data", "Next"}),
    #"Expanded Data" = Table.ExpandListColumn(#"Expanded res", "Data"),
    #"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"}, {"url", "id", "external_id", "via", "created_at", "updated_at", "type", "subject", "raw_subject", "description", "priority", "status", "recipient", "requester_id", "submitter_id", "assignee_id", "organization_id", "group_id", "collaborator_ids", "follower_ids", "email_cc_ids", "forum_topic_id", "problem_id", "has_incidents", "is_public", "due_at", "tags", "custom_fields", "satisfaction_rating", "sharing_agreement_ids", "fields", "followup_ids", "brand_id", "allow_channelback", "allow_attachments"})

in
    #"Expanded Data1"

Thanks ever so much for your help!