Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
twilkdaddy
Frequent Visitor

How to return ALL results in API call

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

7 REPLIES 7
ImkeF
Community Champion
Community Champion

 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"="%%%%"]])),

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 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
Community Champion
Community Champion

 

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?

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

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

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
Community Champion
Community Champion

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.

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

twilkdaddy
Frequent Visitor

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.