Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to Solution.
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
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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |