cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

Having had a closer look at your code and it seems like you're new to the M-language.

But this task requires at least some basic understanding of the M-language. For example what to do with the parameter of a function, as you're not using it in your function FnGetOnePage (video recommendation: https://www.youtube.com/watch?v=GgwXt4LVmsU&t=666s) . To get a basic understanding what's happening in the code you have to build I recommend this video: https://www.youtube.com/watch?v=vhr4w5G8bRA

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

@ImkeF

 

New error

 

api error2.jpg

Super User
Super User

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

Hi @svishwanathan,

sorry, but I cannot help you any further here.

 

This is an advanced topic and there is no standard solution for it currently. I you don't have a basic understanding of the M function, it is very difficult for me to tell you what you have to do. Both functions that might have to be adjusted depend on the actual data that comes from your source and this would mean that I would have to guide you through it step-by-step and you would have to share your screen after each step. This is consulting service in my eyes and I don't do it in the forums anymore.

 

So you might consider opening a new thread on this to raise the chance that someone else picks this up.

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

@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!

Super User
Super User

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

Hi @airfreighter ,

 

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

 

 

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

Hi @ImkeF 

 

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

07-10-2019 13-38-22.jpg


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!

Super User
Super User

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

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.

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

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:

08-10-2019 13-56-12.jpg

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

 

 

 

 

Super User
Super User

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

Hm, that's a bit strange.

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

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




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

@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

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)