Forum Discussion

twilkdaddy's avatar
twilkdaddy
Frequent Visitor
9 years ago

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

  • twilkdaddy's avatar
    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

    • twilkdaddy's avatar
      twilkdaddy
      Frequent Visitor

      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's avatar
        ImkeF
        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?