API returns 1 Product. I need to loop through and return all products into 1 table

Hi Ibendlin,

I've got everything working but it looks like I can'tschedule a refresh because of a dynamic query.

Any sugestions on how to fix this?

This is what I do.

Step 1.  Get the list of ProductIDs

There's an API to get ProductList but it's limited to 100 per page.  I created a simple 1 column table with 10 rows containing 1, 2, 3 , 4 etc.

I invoke the following custom function:

So it passes the value 1, then 2 etc into Pages.  (I'll move on to creating a variable and incrementing the value by one until the function returns zero data later, for now I have hardcoded the pages in the table)

(Pages as text) =>

let

AuthUrl = "https://url?UserName=xxxx&Password=xxxx",

strAPIkey = Json.Document(Web.Contents(AuthUrl)),

Source = Json.Document(Web.Contents("https://url?APIKey=" & strAPIkey & "&PageNo="& Pages))

in
#"Source"

I then expand the list and with some extra steps I end up with just the ProductID.

Step 2:

I then Invoke this function and it gets all the data into a list and I expand it again.

(ProductID as text) =>

let

AuthUrl = "https://url?UserName=xxxx&Password=xxxx",

strAPIkey = Json.Document(Web.Contents(AuthUrl)),

Source = Json.Document(Web.Contents("url" & ProductID &"/Inventory?APIKey=" & strAPIkey))

in
#"Source"

I then expand the table and remove all the columns I don't need.

This all works but is there a better way of doing?  Plus, how can I define strAPIkey as a variable that is used across multiple queries so I only have to call the API once...  The API key is automatically generated every 24 hours, so once at the beginning will be fine.

Any pointers would be greatly appreciated.