Forum Discussion
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
- lbendlinSuper User
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_NewBiFrequent Visitor
Thanks, how would you add the filter?
The API documentation confirms LastID and this code works perfectly for all the other endpoints.