Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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"
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"
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
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
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
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
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"
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
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
@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
Hi @ImkeF
Ah, I see my mistake! Makes sense thank you! Thanks a lot for the swift reply!
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
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:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |