cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Paull Frequent Visitor
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

Accepted Solutions
Super User
Super User

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

Paull Frequent Visitor
Frequent Visitor

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

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

40 REPLIES 40
Super User
Super User

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

Paull Frequent Visitor
Frequent Visitor

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

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

markholland Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

Hi Mark,

you might have to adjust the field names in the code (these are strings in the square brackets) to match what your API returns.

If you need help, please post a picture of the result that your API-call (Source-step) returns.

Cheers, Imke

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

Did I answer your question? Mark my post as a solution!

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




markholland Regular Visitor
Regular Visitor

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

Thanks @ImkeF,

 

Here's a picture of the result I get once I pass the API through Query Editor:

 

01.PNG

 

'time_entries' gives me the 100 records for the last page. 'Links' shows the 3 URLs - first page, second page and last page.

 

I've been playing around with the fields in the square brackets but I'm not totally sure which ones to place where.

 

What would you suggest?

 

Thanks,

Mark

Super User
Super User

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

Hi Mark,

the solution for you is actually simpler than the one provided in this post. As we know that there are 175 pages for you to retrieve, the "only" thing there is to do is to create a list/table of those URLs and call them in an additionall column:

  •  Check and copy the URLs in [links] and see how the page-number is integrated.
  •  Then create a list {1..175} and convert it to a table
  • Add a column where you paste one URL and replace its page number by "& Text.From([Column1]) &" so that the value from the column will replace the hardcoded number in the URL

When you execute that query, all your tables should be loaded into this new column. You can then expand that new column and receive all your data in one table.

If the URL is not constructed in that simple way, we would have to switch to the List.Generate-method instead and expand [links] in every step to retrieve the next URL.

Did I answer your question? Mark my post as a solution!

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




markholland Regular Visitor
Regular Visitor

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

Hi @ImkeF,

 

There are 175 pages at present but this will continue to grow as we get more data.

 

I must admit I'm struggling a little to follow your instructions, apologies. Can you explain how to create a table from my API that will list all pages as they continue to build and build?

 

When I check the URL in [Links] is looks as follows:

 

https://api.harvestapp.com/v2/time_entries?access_token=******&account_id=******&page=1&per_page=100

 

I appreciate the help!

 

Mark

markholland Regular Visitor
Regular Visitor

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

Hi @ImkeF,

 

I found one of your old posts which works a treat:

 

https://community.powerbi.com/t5/Desktop/how-to-create-a-query-that-paginates/td-p/20047/page/3

 

Thanks again for your help.

 

Mark

Super User
Super User

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

Great!

So you can just navigate to the record field "Total Pages" and take this number as a parameter for the lenght of your list to make it automatically adjust to more numbers in the future then.

Cheers, Imke 

Did I answer your question? Mark my post as a solution!

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




Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,764)