How to return ALL results in API call

[twilkdaddy]

I'm new to running queries with API calls in Power BI so please forgive any naivity or obvious errors included in this code.  I've pasted my code below to try and paginate through results, but I'm still only getting a table with 500 rows of data.  When I run the GET query in ARC, the metadata shows resultsAvailable = 16,650......this figure will grow as we get more customers, so I want this query to be dynamic in pulling in any new customers to my Customers Table in Power BI.  

The columns (variables) I'm trying to get into a data table are "contactID", "companyName", "firstName", "lastName".  Below is my attempt at returning all resuts.  The first contactID in the results should be  a value of 200, so I attempted to make that the starting point, but I'm sure I messed up the syntax to make that work correctly.  Any help would be much appreciated!!

PS ---> i hid the Token for security purposes, but let me know if that's necessary for testing code modifications and I can provide.

let
Pagination = List.Skip(List.Generate( () => [contactID = 200 , Counter = 0], // Start Value
each [contactID] <> null and [contactID] <> "", // Condition under which next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://ws-use.brightpearl.com/public-api/dude/contact-service/

contact-search?isCustomer=true&columns=contactId,companyName,firstName,lastName&offset="&Text.From([contactID]), [Headers=[#"brightpearl-app-ref"="dude_powerbi-1", #"brightpearl-account-token"="%%%%"]])),
contactID = if [Counter] <=1 then 200 else WebCall[lastKey], // determine the LastKey for the next execution
Counter = [Counter] + 1, // internal counter

response = WebCall[response],
results = response[results],

#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Column"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "contactID", each List.First([Column])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "nameCompany", each List.First(List.Skip([Column],1))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "firstName", each List.First(List.Skip([Column],2))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "lastName", each List.First(List.Skip([Column],3))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Column"})


],
each [#"Removed Columns"]),1)
in
Pagination

[ImkeF]

 I would have expected the [contactID] to be part of your WebCall - otherwise: How should the WebCall make the next call?

each [ WebCall = Json.Document(Web.Contents("https://ws-use.brightpearl.com/public-api/dude/contact-service/contact-search?isCustomer=true&column...]), [Headers=[#"brightpearl-app-ref"="dude_powerbi-1", #"brightpearl-account-token"="%%%%"]])),

[twilkdaddy]

I don't know why the WebCall was showing as a link and cutting off the entire text.  Please see revised post without the hyperlink.  Thanks!

[ImkeF]

The query would paginate if the result of your WebCall contained a field named "lastKey". Do you really have that field or does the number for the next page sit in a different field/column?

[twilkdaddy]

As far as I can see, there aren't any lastkey fields.  I thought that was some sort of built in variable as a last record identifier.  

Here's a snapshot of the last piece of results yielded out of ARC (-499 is the last array of results containing the desired variable values):

-499:  [Array[4]

[

  0:  768,

   1:  "1986",

  2:  "Krystin",

  3:  "Hand"

],

],

"metaData":

-0:  {

"morePagesAvailable": true,

"resultsAvailable": 16650,

"resultsReturned": 500,

"firstResult": 1,

"lastResult": 500,

"columns": [

 

1:  {

"name": "contactId",

"sortable": true,

"filterable": true,

"reportDataType": "IDSET",

"required": false

}

,

 

2:  {

"name": "companyName",

"sortable": true,

"filterable": true,

"reportDataType": "SEARCH_STRING",

"required": false

}

,

 

3:  {

"name": "firstName",

"sortable": true,

"filterable": true,

"reportDataType": "SEARCH_STRING",

"required": false

}

,

 

4:  {

"name": "lastName",

"sortable": true,

"filterable": true,

"reportDataType": "SEARCH_STRING",

"required": false

}

],

"sorting": [

 

{

"filterable":

{

"name": "contactId",

"sortable": true,

"filterable": true,

"reportDataType": "IDSET",

"required": false

}

,

"direction": "ASC"

}

],

[twilkdaddy]

Any suggestions on how to create or identify that variable to identify the "number for the next page" you mentioned?

Was I provided in the last post helpful at all for troubleshooting where my code problem is?

[ImkeF]

Yes, thank you.

I thought it would be ContactID, but this wouldn' work, as that would skip the range fom 500 until 768 in the example you've given.

Is there another field (like "Result") in the dataset that is 500 for that last record where Contact ID is 768? Then you would have to take this.

[twilkdaddy]

I'm new to running queries with API calls in Power BI so please forgive any naivity or obvious errors included in this code.  I've pasted my code below to try and paginate through results, but I'm still only getting a table with 500 rows of data.  When I run the GET query in ARC, the metadata shows resultsAvailable = 16,650......this figure will grow as we get more customers, so I want this query to be dynamic in pulling in any new customers to my Customers Table in Power BI.  

The columns (variables) I'm trying to get into a data table are "contactID", "companyName", "firstName", "lastName".  Below is my attempt at returning all resuts.  The first contactID in the results should be  a value of 200, so I attempted to make that the starting point, but I'm sure I messed up the syntax to make that work correctly.  Any help would be much appreciated!!

PS ---> i hid the Token for security purposes, but let me know if that's necessary for testing code modifications and I can provide.

let
Pagination = List.Skip(List.Generate( () => [contactID = 200 , Counter = 0], // Start Value
each [contactID] <> null and [contactID] <> "", // Condition under which next execution will happen
each [ WebCall = Json.Document(Web.Contents("https://ws-use.brightpearl.com/public-api/dude/contact-service/contact-search?isCustomer=true&column...]), [Headers=[#"brightpearl-app-ref"="dude_powerbi-1", #"brightpearl-account-token"="%%%%"]])),
contactID = if [Counter] <=1 then 200 else WebCall[lastKey], // determine the LastKey for the next execution
Counter = [Counter] + 1, // internal counter

response = WebCall[response],
results = response[results],

#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Column"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "contactID", each List.First([Column])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "nameCompany", each List.First(List.Skip([Column],1))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "firstName", each List.First(List.Skip([Column],2))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "lastName", each List.First(List.Skip([Column],3))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Column"})


],
each [#"Removed Columns"]),1)
in
Pagination