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

[cartman21]

Hello @ImkeF ! Thanks for taking the time to help me:

Here is what I have based on your help:

The relevant urls are in a table named Table2

let
//Table2
 Source = #table({"Start", "Finish"}, {{0, 500},{501, 1000},{1001, 1500}, {1501, 2000}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish])))
//Function
Function = (Start as text, Finish as text) =>
let
 Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Limit&"&api_token=xxxxxxxxxxxxxxxx")),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
 #"Expanded Column1"

I unfortunately get a "Token Comma Expected" Error. Are you quickly able to tell me where I have gone wrong?

[ImkeF]

My fault, sorry - didn't test the query. Pls try this:

let
//Table2
 Source = #table({"Start", "Finish"}, {{0, 500},{501, 1000},{1001, 1500}, {1501, 2000}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
Function = (Start as text, Finish as text) =>
let
 Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxxxxxxxxxx")),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
 #"Expanded Column1"
in 
CallFunction

[cartman21]

Hello again @ImkeF

Something seems to work, but it breaks for values 1500 and beyond, where I get an error.

The error is Expression.Error: We cannot convert the value null to type list.

Details

Value = 

Type=Type

Which I don't understand

Further, while the code holds up for values 0-1500, most of the values are null, but when the query is run individually, I get values in the tables.

When I run to advanced query, and expand the tables, this is what shows up:

let
//Table2
 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}, {1500, 1999}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
Function = (Start as text, Finish as text) =>
let
 Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxx)),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
 #"Expanded Column1"
in 
CallFunction

[ImkeF]

Unfortunately that's a mess now. Please start with this query again. I've reordered the steps to make further expansions easier for you:

let

Function = (Start as text, Finish as text) =>

let
 Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxx")),
 data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
 #"Expanded Column1",

//Table2
 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}, {1500, 1999}}),
//CallFunction
 CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))),
//Function
    #"Expanded CallFunction" = Table.ExpandTableColumn(CallFunction, "CallFunction", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {"CallFunction.id", "CallFunction.company_id", "CallFunction.user_id", "CallFunction.done", "CallFunction.type", "CallFunction.reference_type", "CallFunction.reference_id"})
in
    #"Expanded CallFunction"

[ImkeF]

With regards to the errors above 1500 you can inject an error-handler like this:

CallFunction = Table.AddColumn(Source, "CallFunction", each try Function(Text.From([Start]),Text.From([Finish])) otherwise #table({"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {})),

This returns a blank table with your column names in case the webcall fails.

[ImkeF]

null returns could be due to wrong field names in the expansion of the record. You could try this:

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Record.FieldNames(#"Converted to Table"[Column1]{0}) )

this will expand all fields that have the same field names as the record of the first row

[moritz1]

Hello!

After reading most of the Thread and a couple of hours of try and error later, frustration and mind block starts to kick in for me.

I am trying to adapt the code samples to request data from Airtable.com, which uses the same Pagination with an offset-Parameter like in the last example. Maybe someone can help me?? 

To make the reproduction easier, I created a sample database with five entries.

Example how the API works, I am requesting only two records (with the pageSize=2 Parameter) to allow testing with minimal data...

// 20170424221134
// https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2

{
  "records": [
    {
      "id": "rec9xyewCK97T01kP",
      "fields": {
        "Room": "Garage",
        "Condition": "Poor",
        "Priority": "Low"
      },
      "createdTime": "2017-04-24T19:53:01.511Z"
    },
    {
      "id": "recZmk4a5kmxdfK7O",
      "fields": {
        "Condition": "Poor",
        "Priority": "Medium",
        "Room": "Kitchen"
      },
      "createdTime": "2015-11-16T22:48:35.000Z"
    }
  ],
  "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O"
}

The offset-Parameter can be inserted in the next call, to get the next two records:

https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O

To request the whole dataset (without pagination), I would perform the following in Power BI

let
    Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")),
    records = Source[records],
    #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"})
in
    #"Expanded Column1.fields"

The thing is, the offset parameter must be valid and cannot be left empty. For the first API-Call, it must not be sent at all.

I tried to bring together this coding and the example coding from earlier:

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 [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"records", "offset"}, {"Column1.records", "Column1.offset"}),
    #"Erweiterte Column1.records" = Table.ExpandListColumn(#"Erweiterte Column1", "Column1.records"),
    #"Erweiterte Column1.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.records", "Column1.records", {"id", "fields", "createdTime"}, {"Column1.records.id", "Column1.records.fields", "Column1.records.createdTime"})
in
    #"Erweiterte Column1.records1"

The thing is now, that the first API call seems to be executed twice, at least I get the first two values doubled in in the result set.

Is there any way to "initialize" the Last_Key and only pass the offset-Parameter if the Last_Key is not initialized?

I tried this:

let
Pagination = List.Skip(List.Generate( () => [Last_Key = "&init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<=1 then "" else "&offset="[WebCall][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2"&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
),1),

Which would send an empty "init"-parameter on the first call (the API doesn't mind that), and concatenate the offset-Parameter later. But this only returns the first two entries twice.

Can anyone help me? Thank you very much!! 

Best regards

Moritz

[ImkeF]

It's late, so just a quick idea now & maybe more tomorrow 🙂

You have to put a condition into the step "WebCall", like :

if counter= 0 then ...CallWithoutOffset/LastKey else YourCurrentString

[moritz1]

Hello Imke,

thanks for your reply. That did the trick! And the Counter<=1 had to be changed to Counter<1... Thanks a lot!

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>&pageSize=2")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>pageSize=2&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1),
    #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}),
    #"Erweiterte Column1.Value" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.Value", {"records", "offset"}, {"Column1.Value.records", "Column1.Value.offset"}),
    #"Erweiterte Column1.Value.records" = Table.ExpandListColumn(#"Erweiterte Column1.Value", "Column1.Value.records"),
    #"Erweiterte Column1.Value.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records", "Column1.Value.records", {"id", "fields", "createdTime"}, {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}),
    #"Erweiterte Column1.Value.records.fields" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records1", "Column1.Value.records.fields", {"Condition", "Priority", "Room"}, {"Column1.Value.records.fields.Condition", "Column1.Value.records.fields.Priority", "Column1.Value.records.fields.Room"})
in
    #"Erweiterte Column1.Value.records.fields"

[Robi2]

Hi All,

I just wanted to share some code I wrote to get data out of elastic search using its scan and scroll pagination.

So if you have lots of data in elastic search, you must grab it in batches, and this code does exactly that, perhaps it will be helpful to someone:

let
FnGetElasticData = (url as text) as list =>
let
	// init params
	size=10000, // the size of the "chunks" of data that will be returned on each batch
	scroll = "1m", // the time in minutes elastic search should keep the "context" alive
	// build url for first fetch
	operator = if Text.Contains(url, "?") then "&" else "?",
	initUrl = url & operator & "scroll=" & scroll & "&size=" & Text.From(size),
	
	// get first batch of results
	initSource = Json.Document(Web.Contents(initUrl, [IsRetry=true])),
	totalResults = initSource[hits][total], // the total number of results to return
	iterations = Number.IntegerDivide(totalResults, size), // the total number of iterations we need to do
	
	// build url for scroll
	scrollId = initSource[_scroll_id], // the scroll id with wich we will fetch the rest of the results
	uriParts = Uri.Parts(url), // for getting the host and scheme of the url
	scrollUrl = uriParts[Scheme] & "://" & uriParts[Host] & "/_search/scroll?scroll=" & scroll & "&scroll_id=" & scrollId,
	
	// this will return a scrolled result from elastic search
	FnGetScrolledPage = (url as text) as list =>
		let
			response = Json.Document(Web.Contents(url, [IsRetry=true])),
			data = response[hits][hits]
		in
			data,
	
	// now loop through all of the iterations and return the data
	resultsList = List.Generate(()=>[i=0, res=initSource[hits][hits]], // Set inital data
		each [i] <= iterations, // Keep going until all of the iterations have been done or there is no more data
		each [i=[i] + 1, res = FnGetScrolledPage(scrollUrl)], // Get next batch of results		
		each [res])
in
    resultsList
in
FnGetElasticData

Basically, it will create a function that you can call with the URL for your elasticsearch query and it will return all of the results using paging.

Please note that this code will not work in automatic refreshes using the Power BI Gateway because that expects static URLs, you can get over this pretty easily by changing the code above to include the hard-coded URLs and then scheduled refresh will work.

[Anonymous]

Hi,

I have a similar issue where by I am trying to retreive from a web api but the pagination is only set to 100.

My data is Xml, but I have noticed that the above discussion is related to json, so not sure if this will affect how the code needs to be written for me.

I need to retreive around 300,000 records but the pagination only returns 100 records at a time.

Here is what the URL looks like:

http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&pa...

How can I make it so PBI returns all the records, not just 100 at a time?

Thank you

Mike

[ImkeF]

Hi Mike,

if the URL for the next page looks like so:

http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&pa... ,

everything you have to do is to create a table with one row per necessary call:

Table.FromColumns({{1..300000/100}})

Add another column where you reference the first column as a parameter/variable to your web call:

Web.Page(Web.Contents("http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1])))

 It doesn't matter in what format the result will be returned. Build a function that retrieves one of it and apply it as a next step to every (returned results in each) row. 

[ngazelle]

Hi,

Is there a possibility to have the max value ("300000") taken from a previous action or a variable inteaed of having the entire serie "1..300000/100" hard coded?

The idea would be to execute a call to get the max then create the table and iterate the calls.

Regards.

[Anonymous]

@ngazelle you can create a function on the query which creates that value(300000). and call that function wherever needed

[Anonymous]

@ngazelle you can create a function on the query which creates that value(300000). and call that function wherever needed

[ImkeF]

Yes. I'm currently working on a blogpost to describe exactly this. Will post linke once finished.

[nerd_in_NE]

lmkeF,

Thank you so much for being such a great resource - it is truly appreciated!!!

I am in a bit of a predicament... I put together a project for a client that pulls data from Airtable via an API into Excel. I am completely finished with the project, except I just discovered that Airtable is only able to pull 100 records at a time! I have been reading through these posts, but I still can't seem to resolve the pagination issue. There are currently almost a 1,000 records and the number of records will grow. I apologize, I'm a bit of a novice writing code in this language!

I copied and pasted your code directly into my query, but I'm getting an error (see below). What am I doing wrong? I'm supposed to send this in to the client tomorrow morning so I'm hoping you see this soon (crossing fingers)! Thank you again for your expertise!

ERROR: Expression.SyntaxError: Token Comma expected. (See code in red below)

let
LINK = "https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE",
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 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(LINK],Query=[page=Text.From([Pages])]])),
// retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = try Table.FromRecords(WebCall[events]) otherwise Table.FromList({}) // steps of your further query
                      ]   ,
                each [Table] 		
), 1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

[DBa]

Hi @nerd_in_NE

At a first glance, I am fairly certain there is something wrong with your link, as the page argument is nowhere in it. 

The web contents row should look something like this (highlighted in red) :

each [ WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE&page=1",Query=[page=Text.From([Pages])]])),

However, please keep in mind that the format is highlighy dependent on the API you are calling. (i.e. how would you call the first page in the api? is 'page' even the argument you would need or is it called somehow else?)

Additionally, the following part of the query is also dependent on your API:

Table = try Table.FromRecords(WebCall[events])

 More specifically, the [events] name will depend on the name of the record you are extracting (it can be anything, depending on your api)

[nerd_in_NE]

DBa,

Thank you very much for the quick reply!!!

I was actually able to resolve my issues by utilizing a solution posted by "Mo_re" in the post below.

https://community.airtable.com/t/json-string-only-contains-100-rows-when-connected-to-power-bi-deskt...

For convenience, here's the code:

let
    Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
   		each  [Last_Key] <> null, // Condition under which the next execution will happen
   		each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
                       WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call
    		       Counter = [Counter]+1// internal counter
                      ],
   		each [WebCall]
    ),1)
in
    Pagination

[Anonymous]

Hi ImkeF,

I'm getting an error message around 

&Text.From([Column1]))

Expression.Error:There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression? 

Regards

Mike

[ImkeF]

Are you using it within a "Table.AddColumn"-Command?

If yes, please share full query code