Forum Discussion

Rolo_NewBi's avatar
Rolo_NewBi
Frequent Visitor
3 years ago

Power Query API error

Hi. 

Im rather new to PowerBi and would appreciate some help with an error I've been getting. 

 

I am calling the web API to bring in Product info from Repsly and this is the code that I am using: 

 

let
Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://[email protected]/v3/export/products/0", [Headers=[Accept="application/json"]])),
TotalCount = 1

],
each [TotalCount]>0,
each
[

Result= Json.Document(Web.Contents("https://[email protected]/v3/export/products/" & Text.From([Result][MetaCollectionResult][LastID]), [Headers=[Accept="application/json"]])) ,
TotalCount = [Result][MetaCollectionResult][TotalCount]

],

each [Result][Products]
),
FinalTable = Table.FromRecords(List.Combine(Final))
in
FinalTable

 

The API is limited to 50 rows so this loops the API and brings in all the required information. I have investigated and the API has an issue that creates an additional blank row or a "null" value row at the end of the product list. 

This means that I am getting in all the product rows but it creates an error in the bottom row right below the final value. 

I have tried to remove the error by using standard options like "remove error/row etc" and replace values but it does not work. 

 

Screenshot of the Error below: 

 

 

 

I assume I have to eliminate the error in my call to the API. 

Can anyone assist with how I need to incorporate this into my code?  

 

2 Replies

  • change line 23 to filter out the null values. You may also want to review your code. Aren't you supposed to use NextID rather than LastID ?

    • Rolo_NewBi's avatar
      Rolo_NewBi
      Frequent Visitor

      Thanks, how would you add the filter? 

      The API documentation confirms LastID and this code works perfectly for all the other endpoints.