Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |