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

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.

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

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

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

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 

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

Hi @ImkeF,

 

There always seems to be a hurdle somewhere. Apparently you can schedule a refresh on a dataset with pagination in the M Language, which is the main part I'm trying to achieve.

 

Do you know any ways around this?

 

Mark

Yes Mark, that's an issue. If you skip to the end of the thread that you've mentioned, you'll find a solution for it 🙂

Or check this article which has a very detailled description/solution: http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

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

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.