how to create a query that paginates?

[gotmike]

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...

[Aracelli]

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.

[mwoody1983]

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

[DBa]

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?

[ImkeF]

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)}

[Anonymous]

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.

[Anonymous]

Resolved - Million thumbs up  @ImkeF for your help.

Your solution is working smoothly. 

Can't thank you enough.

Regards

emudria 

[Anonymous]

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

[Anonymous]

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.

 let

Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value

        each  [Last_Key]<> null , // Condition under which the next execution will happen

        each [ WebCall = try Json.Document(Web.Contents(BaseUrl & "?pageSize=1000&" & "pageNumber=" & Text.From(Counter) & "&scrollId="&Last_Key,Token)) otherwise null, // retrieve results per call

                Last_Key = WebCall[scrollId],

                Counter = [Counter]+1,// internal counter

                Table = Table.FromRecords(WebCall[results]) // steps of your further query

                //Value = #"Converted to Table"{0}[Value] // last step of your further queries

                      ] 

        ,each [Table]

) ,1),

    Custom1 = Table.Combine(Pagination)

in

    Custom1

[Grant]

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&amp;page=1%7B%22filter%2...

Hope someone can help

Kind Regards - Grant

[ImkeF]

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...

[spocx]

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. 

[ImkeF]

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"

[spocx]

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

[ImkeF]

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"

[spocx]

Hi ImkeF

I still have the exact same errors as mentionened in my previous post. 

Best Regards,

[ImkeF]

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"

[spocx]

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:

[ImkeF]

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"

[spocx]

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:

[ImkeF]

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.

[spocx]

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: