Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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!
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]
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?
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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
34 | |
27 | |
26 |
User | Count |
---|---|
98 | |
96 | |
60 | |
44 | |
40 |