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.
I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the results.
For instance, if you send:
https://api.hubapi.com/deals/v1/deal/all?hapikey=demo
at the very end of the query, you see the following JSON:
"hasMore":false "offset":27939158
so, if hasMore is true, the NEXT query should look like this:
https://api.hubapi.com/deals/v1/deal/all?hapikey=demo&offset=27939158
and then, we would want to repeat the process until hasMore comes back with false.
i'm completely new to power bi, so would love to know how to handle this type of query process.
in another language, this would just be do { } while (hasMore == false);
or something like that...
Hi thanks! I already managed to paginate the load of my input data, but now I have an error when I want to perform an automatic update in PowerBI WEB.
Apparently I have a very long sentence, but I do not know how I can solve the problem
I would really appreciate if you can help me.
Thank you.
Hi ImkeF,
How would I go about doing this with a JIRA call, where I have a startAt and total number of records? I've included them below as an example, I can't get my head around how I would call them? Any help pointing me in the right direction would be great thanks.
let Pagination = startAt": 0 "total": 161 each [Last_Key] <> null and [Last_Key] <> "", // Condition under which the next execution will happen each [WebCall = Json.Document(Web.Contents("https://companyName.atlassian.net/rest/agile/1.0/board?startAt"&??,[Headers=[ContentType="application/json", Authorization="Auth="]])), // retrieve results per call Last_Key = if [Counter]<=1 then 1 else WebCall[lastKey] ,// determine the LastKey for the next execution Counter = [Counter]+1,// internal counter #"Converted to Table" = Record.ToTable(WebCall), // steps of your further query Value = #"Converted to Table"{1}[Value] // last step of your further queries ], each [Value]),1), Pagination1 = Pagination{0} in Pagination1
Not as neat as the previous query, but you could try something along these lines:
Source1 = {0..161}, #"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Add Pagination Number" = Table.AddColumn(#"Converted to Table", "startAt", each [Column1]+1), #"Added Custom" = Table.AddColumn(#"Add Pagination Number", "Custom", each Json.Document(Web.Contents("yourlink?startAt=0", [Headers=[your details],Query=[startAt=Text.From([startAt])]]))),
in
#"Added Custom"
I'm not sure whether you actually need 'startAt' in the api link, but you can play around with removing it to get it to work?
Agree with @DBa: No need to use the complicated List.Generate here. "Just" create a table with one row per api-call needed that holds all the variables/parameters that are needed to create the distinct URLs.
If one call can fetch 50 records, then your table might just need four rows and you could start with a list like this:
{1..Number.RoundUp(161/50)}
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 & Community,
Need help with a similar problem.
Same as rest, started from DataChatn's blog and got stuck in some implementation.
https://datachant.com/2016/06/27/cursor-based-pagination-power-query/
I am trying to iterate over an API source, to fetch some data(pagination).
I have to do a "POST" request, and in the "content/query parameters", provide information for the next page (not part of url)
this information goes in as parameter named "AFTER" ( as seen in below snapshot- this is the value that we get from [data][next] response - keep reading)
In the responce of any query, I get three fields.
[data][post] --> this is my data part,
[data][remaining] -->count of entries left,
[data][next]-->a 64base code to be placed in the next query paramter to tell, starting point for next fetch
below documentation for reference:
The first query need to go either without "after" parameter, or we can set it to a stationary value. later queries can go and use "next"'s value for "after".
I have tried to do following - can someone please see, if there are some evident errors I have made here
let iterations = 10000 // just for initialization auth_key ="Basic myAuthKey", url = "https://api.socialbakers.com/0/facebook/page/posts", header= [#"Authorization" = auth_key, #"Content-Type" = "application/json; charset=utf-8"], content= "{ ""date_start"":""2017-08-16"", ""date_end"":""2017-11-10"", ""profile"":""88147621212"", ""fields"":[""id"",""created"",""message ], ""limit"":5, ""after"":[next] // Not sure if this is the right way to use the next value here }", // DO I need to pass just URL or Content as well in this method & define header wihtin the method? FnGetOnePage = (url) as record => let Source =Json.Document(Web.Contents(url,[Headers = header,Content = Text.ToBinary(content)])), data = try Source[data][posts] otherwise null, next = try Source[data][next] otherwise "somefixedvalue?", remaining = try Source[data][remaining] otherwise null, res = [Data = data, Next = next,rem = remaining] in res, GeneratedList = List.Generate( ()=>[i=0,res=FnGetOnePage(url)], each [i] <iterations and [res][rem]>0,
// Not sure about the next two lines, if they are making sense in my scenario-
// Depending how we designed our method - really confused about these each [i=[i] +1 , res = FnGetOnePage([res][Next])], each[res][Data]) in
GeneratedList
Any help is this reagrd highly appriciated.
Cheers.
Resolved - Million thumbs up @ImkeF for your help.
Your solution is working smoothly.
Can't thank you enough.
Regards
emudria
Hi @Anonymous
Came across your post in on this thread from a few years back. I have a very similar endpoint that I have not been able to figure out how to iterate (for about 6 months now), and was hoping you would be willing to share the solution provided to you by @ImkeF.
Much appreciated!
C
edit: the original problem statement referenced the challenges of iterating an endpoint that required the token be embedded in the body of the query in order to retrieve the paginated data, rather than inserted in the URL as a parameter
Hi All,
I am struggling with slightly different pagination issues. I need to use scrollId field from the last call to pass into subsequent calls. I am not able to make the sample discussed in this thread work in my scenarios. below is my code thus far. Can someone please review and let me know what I doing wrong here.
Hi
Apologies if this is construed as a cross post. if I have a working example of a query that paginates that Imke helped me with - see below.
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], each Table.RowCount([Table])>0 or [Counter]=0, each [ WebCall = Json.Document(Web.Contents("https://api.capsulecrm.com/api/v2/opportunities?perPage=100&embed=tags&page="&Text.From([Page])&"",[Headers=[Authorization="Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"]])), Page = [Page]+1, Counter = [Counter]+1, Table = Table.FromRecords(WebCall[opportunities]) ] ,each [Table] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
So what I'm trying to acomplish now, is create a query that paginates based on the query above but Posts a filter in the body of the request. To put it bluntly, it's doing my head in, so I'm hoping that someone can assist? It uses a different URL however, https://api.capsulecrm.com/api/v2/opportunities/filters/results and I need to post the following in the body of the request;
{ "filter" : { "conditions": [ { "field": "isClosed", "operator": "is", "value": false } ] }}
This is what I have coded thus far.
let
obj = "{""filter"":{""conditions"":[{""field"":""isClosed"",""operator"":""is"",""value"":false }]}}",
authKey = "Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
url = "https://api.capsulecrm.com/api/v2/opportunities/filters/results?perPage=100&page=",
Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0],
each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen
each [ WebCall = Json.Document(Web.Contents(url&Text.From([Page]) & obj,[Headers=[#"Authorization"=authKey, #"Content-Type"="application/json"],Content = Text.ToBinary(obj)])),
Page = [Page]+1,
Counter = [Counter]+1,// internal counter
Table = Table.FromRecords(WebCall[opportunities])
]
,each [Table]
) ,1)
in
Pagination
Obviously the code above does not work, otherwise I wouldnt be posting here . I do however think I'm on the right track. See error produced below;
DataSource.Error: Web.Contents failed to get contents from 'https://api.capsulecrm.com/api/v2/opportunities/filters/results?perPage=100&page=1%7B%22filter%22:%7...' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.capsulecrm.com/api/v2/opportunities/filters/results
Url=https://api.capsulecrm.com/api/v2/opportunities/filters/results?perPage=100&page=1%7B%22filter%2...
Hope someone can help
Kind Regards - Grant
Please check out this post and see if you can get it working: https://eriksvensen.wordpress.com/2014/09/15/specifying-json-query-in-power-query-example-statistic...
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
I am as well a total novice joining this great thread.
I am trying to use the code you helped Grant to build earlier in this thread to accomplish a looped rest api call.
I have basically just changed the url in the webcall and the Pagination values that is defined for the page i am trying to get data from. The Pagination values for the system can be found here: https://developer.itrp.com/v1/general/pagination/
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1,// internal counter Table = Table.FromRecords(WebCall[requests]) // steps of your further query ] ,each [Table] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
But i recieve the following error when trying to convert to table:
Any kind of help is much appreciated.
Please try with additional curly brackets in "Table" like so:
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1,// internal counter Table = Table.FromRecords( { WebCall[requests] } ) // steps of your further query ] ,each [Table] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to 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 ImkeF
Thank you so much for helping me out. It is highly appreciated. Unfortunately I still get the same error.
First I only see one row and a error when looking at the paginated call:
And if I try to turn it into a table i get the following error:
Best Regards
Then try the following please:
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1,// internal counter Table = WebCall[requests] // steps of your further query ] ,each [Table] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to 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 ImkeF
I still have the exact same errors as mentionened in my previous post.
Best Regards,
Next step is this:
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1// internal counter ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to 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
I added a "]" in the code, because I got "Token Comma" syntax error. So the code looks like this:
let Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Page = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1// internal counter ] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
And now I recieve the following error:
Sorry, I should have deleted that in the first record as well like this:
let Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1// internal counter ] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to 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
That is perfectly allright ImkeF:-) I am just grateful, that you want to help me out.
With the last code you posted, I get the following error:
Hm, let's see if there will be any code left 🙂
let Pagination = List.Skip(List.Generate( () => [Page = 1, Counter=0], // Start Value each [Counter]<5, // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents("https://api.itrp.qa/requests?per_page=100&page="&Text.From([Page])&"")), // retrieve results per call Page = [Page]+1, Counter = [Counter]+1// internal counter ] ) ,1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
Aim is to get a list of responses and then to see which further transformations are needed and how to define the looping-condition.
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
Now we got a hit:-) I am able to turn it in to table and expand the columns. I am just not seeing the number of columns and values as i would expect from the data source:
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |