Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Paull
Frequent Visitor

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

Hi Everyone,

I am a newbie on powerBi and would need your help to figure out how to automate a rest-API call with a Json response having several pages.
The initial URL is the following:
https://api.higring.com/v2/reporting/get?period=custom_date&start_date=2017-01-01&end_date=2017-07-2... &group_by=date,platform,custom_1,custom_2, type,ad_format,connection,country&filter[]=network:network1

I get the following response:

capture reponse.JPG

The idea would be to automatically call the “next_page_url” until there is no “next_page_url”.
Any thoughts on how I can process this ?

Thanks a lot

2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

This video will show you: https://www.youtube.com/watch?v=vhr4w5G8bRA&t=6s

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Paull
Frequent Visitor

Hi ImkeF,

 

Thanks a lot for your reply. It helps a lot.
I generate a script that do pretty much the same than the video. 
It looks like this:

let
 Source = Json.Document(Web.Contents(url, [Headers=[Authorization="your token"]])),
 iterations = Source[total_pages],          // get the information within the response
 url = "you URL", // here goes your URL
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="yourtoken"]])),
    data = try Source[connections] otherwise null, //get the data of the first page
    next = try Source[next_page_url] otherwise null, // the script ask if there is another page
    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]),
  #"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converti en table"

It works perfectly.

Have a good day,

 

Paul

View solution in original post

47 REPLIES 47
FPackermanGTA
Frequent Visitor

OK I have a similar issue trying to get this to work.

 

I have an api which will only return 200 records but rather than the next url it gives me the lastid which I think I can tag onto the url to then give me the next page of results. So the simple code below return one page of data and not sure what the best way would be to go about doing this.

 

 

let

     apiUrl = "myUrl",

     options = [Headers =[
					#"Accept"="application/json",
					#"Authorization"="myKey"]],

     result = Web.Contents(apiUrl , options),
    #"Imported JSON" = Json.Document(result,65001)
in
    #"Imported JSON"

 

FPackermanGTA_0-1631023511841.png

The url would then look like this to search the next set and so on. I think thats how it would work anyway.

 

https://myURL?after_id=1494

 

The API says

Pass the last id received, to receive records after it. Null can be passed to start at the beginning.

 

Any help much appreiciated and I know this is an old thread but here goes.....

 

Cheers

 

Paul.


@FPackermanGTA wrote:

OK I have a similar issue trying to get this to work.

 

I have an api which will only return 200 records but rather than the next url it gives me the lastid which I think I can tag onto the url to then give me the next page of results. So the simple code below return one page of data and not sure what the best way would be to go about doing this.

 

 

 

let

     apiUrl = "myUrl",

     options = [Headers =[
					#"Accept"="application/json",
					#"Authorization"="myKey"]],

     result = Web.Contents(apiUrl , options),
    #"Imported JSON" = Json.Document(result,65001)
in
    #"Imported JSON"

 

 

FPackermanGTA_0-1631023511841.png

The url would then look like this to search the next set and so on. I think thats how it would work anyway.

 

 

https://myURL?after_id=1494

 

 

The API says

Pass the last id received, to receive records after it. Null can be passed to start at the beginning.

 

Any help much appreiciated and I know this is an old thread but here goes.....

 

Cheers

 

Paul.


And does it?

Yes I just need a way to recursively enter the function with the previous 'last_id' appended to the base url until 'additional_pages' is False and then join it all together but struggling to do it. Any ideas?

So the bit of code that loops is this section

 

    // define the function
    FnGetOnePage =
        (url) as record =>
        let
            Source = Json.Document(Web.Contents(url)),
            data = try Source[entry] otherwise null,
            // link is list of records
            link = try Source[link] otherwise null,
            // link{0} could be "self", "next"
            next = if link{0}[relation] = "next"
                    then link{0}[url]
                    else null,
            res = [Data=data, Next=next]
        in
            res,

 

 

I think the GeneratedList code can stay the same for you, but the FnGetOnePage portion needs to change. Use the Json.Document function to convert your url. This will serve as the "top level" - the line that starts with Source = in the FnGetOnePage. data is the variable that holds your results - so something like data = Source[Data]. next is the variable that holds your iterating logic - so something like next = data[lastid].

This is psuedo code of course so you will have to experiment to find out what your specific needs are.

I chanced upon this today and think might be useful for some  https://www.youtube.com/watch?v=g5bo_UAQjIE&t=454s

TerriblyVexed
Frequent Visitor

Thank you all so much for this!!!

One point of clarification - The GeneratedList had a typo in it about half way through the discussion.

each [res][Next] <> null

as the evaluator function should be

each [res][Data] <> null

it was this way in several posts and then got switched.

 

I would like to share my version for anyone struggling with Fhir data.

let 
    baseUrl = "https://{myurl}.azurewebsites.net",
    resource = "/Questionnaire",
    search = "?&_total=accurate",
    fullUrl = baseUrl & resource & search,

    // define the function
    FnGetOnePage =
        (url) as record =>
        let
            Source = Json.Document(Web.Contents(url)),
            data = try Source[entry] otherwise null,
            // link is list of records
            link = try Source[link] otherwise null,
            // link{0} could be "self", "next"
            next = if link{0}[relation] = "next"
                    then link{0}[url]
                    else null,
            res = [Data=data, Next=next]
        in
            res,
            
    // use the FnGetOnePage function with List.Generate    
    GeneratedList =
        List.Generate( ()=> 
            [result = FnGetOnePage(fullUrl)],
            // do while
            each [result][Data] <> null,
            // each row of list
            each [result = FnGetOnePage([result][Next])],
            // output
            each [result][Data]
            ),
    // convert to table from the output of function GeneratedList
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expand Table" = funcExpand(GeneratedList)
in
    #"Expand Table"

This uses not only the functions listed here but also the funcExpand function which auto expands all columns that have data.

 

@ImkeF  - thank you!

 

Vexed

ImkeF
Super User
Super User

This video will show you: https://www.youtube.com/watch?v=vhr4w5G8bRA&t=6s

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Paull
Frequent Visitor

Hi ImkeF,

 

Thanks a lot for your reply. It helps a lot.
I generate a script that do pretty much the same than the video. 
It looks like this:

let
 Source = Json.Document(Web.Contents(url, [Headers=[Authorization="your token"]])),
 iterations = Source[total_pages],          // get the information within the response
 url = "you URL", // here goes your URL
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="yourtoken"]])),
    data = try Source[connections] otherwise null, //get the data of the first page
    next = try Source[next_page_url] otherwise null, // the script ask if there is another page
    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]),
  #"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
  #"Converti en table"

It works perfectly.

Have a good day,

 

Paul

Hello, Thank you for the code.
I tried the Do-while code for iterations but I do not get any results. The issue could be the Merakki API URL doesn't consists of page numbers instead it has a timespan. The codes are as follows - 

(page as number) as table =>
let
    Source = Json.Document(Web.Contents("https://api.meraki.com/api/v1/networks/(Academy ID)/clients?timespan=2678400&perPage=1000" ,[Headers=[#"(Mearkki Header)"="(Organization API Key)",))),
    Data1 = Source{1}[Data],
    RemoveBottom = Table.RemoveLastN(Data1,3)
in
    RemoveBottom

let
    Source = List.Generate( () =>
  [Result = try GetData(1) otherwise null, Page = 1],
  each [Result] <> null,
  each [Result = try GetData([Page]+1) otherwise null, Page = [Page]+1],
  each [Result])
in
    Source

 Thanks in advance

Hi,

 

I'm having real difficulties following the steps in this post with my API. I've watched the video, which differs from the code given at the bottom of this post. Neither approach has worked for me. I'll go through what I've done and hopefully someone can help me:

 

Following the video I've added the following code:

 

GetData

 

(page as number) as table =>
let
    Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/time_entries?access_token=********&account_id=********&page=" & Number.ToText(page))),
    Data1 = Source{1}[Data],
    RemoveBottom = Table.RemoveLastN(Data1,3)
in
    RemoveBottom

 

I've then copied the code to create the list of pages:

 

let
    Source = List.Generate( () =>
  [Result = try GetData(1) otherwise null, Page = 1],
  each [Result] <> null,
  each [Result = try GetData([Page]+1) otherwise null, Page = [Page]+1],
  each [Result])
in
    Source

 

But when I do this I get nothing, just a column header with List but no list of pages.

 

When I try the amended version in this post I get nothing either. Here's the code I'm using:

 

let
 Source = Json.Document(Web.Contents(url, [Headers=[Authorization="********"]])),
 iterations = Source[total_pages],          // get the information within the response
 url = "https://api.harvestapp.com/v2/time_entries?access_token=********&account_id=********", // here goes your URL
 
 FnGetOnePage =
  (url) as record =>
   let
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="********"]])),
    data = try Source[connections] otherwise null, //get the data of the first page
    next = try Source[next_page_url] otherwise null, // the script ask if there is another page
    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"

I really have no idea where I'm going wrong. Can anyone help? @Paull @ImkeF

 

 

Thanks,

Mark

@Paull@ImkeF

 

I am trying to call APIs from Freshservice. After many weeks, I finally was able to call the site which returned only 30 records. I need to be able to paginate. I have visited every single link mentioned on the blogs but I am not able to follow...can any of you please see what I am doing wrong here

 

Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorizati... xxxxxxxxx", #"Content_Type"="application/json"]])),
iterations = Source[total_pages], // get the information within the response
url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])),
Page = try Source[connections] otherwise null, //get the data of the first page
next = try Source[next_page_url] otherwise null, // the script ask if there is another page
res = [Page=page, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Page]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [Page][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"

api error.jpg

You're missing the let-keyword at the beginning:

 

let Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages",[Headers=[Authorizati... xxxxxxxxx", #"Content_Type"="application/json"]])),
iterations = Source[total_pages], // get the information within the response
url = "https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", // here goes your URL

FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://swinerton.freshservice.com/helpdesk/tickets/filter/all_tickets/?pages", [Headers=[Authorization="xxxx", #"Content_Type"="application/json"]])),
Page = try Source[connections] otherwise null, //get the data of the first page
next = try Source[next_page_url] otherwise null, // the script ask if there is another page
res = [Page=page, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Page]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [Page][Data]),
#"Converti en table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converti en table"

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF

 

New error

 

api error2.jpg

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

 

 

 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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!

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.