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

[Tommy____G]

Hi,

Problem Statement

I need a full inventory of stock and there's an API I can use but I have to pass a single Product ID.  I have a list of Product IDs so I want to be able to loop through the Product IDs, return the data and put it all in a single table. 

The ask

What's the most effiecent way of doing this?

My thought process

I create exactly the same tables.  One is called FullInventory, the other SingleProduct.

I call the API and populate the SingleProductTable

I then use an append query to populate the FullInventory

I call the API using another ProductID and populate the SingleProductTable

I then use an append query to populate the FullInventory

and so on until I have run through all the Product IDs

I'll be honest, this doesn't sound very effecient.  Plus, I'll need to ensure there's a delete all data from the FullInventory table before I process the full query

Any suggestions would be greatly appreciated.

Thanks 

Tom

[lbendlin · Accepted solution]

No need to delete anything - Power Query always starts from scratch.

The standard approach is

- have  a list of Product IDs

- construct the URL for each of them

- use Web.Contents() to fetch the data for each

- expand the result into additional columns

It will still have to call the API for each individual product which sounds cumbersome, but that seems to be your environment restriction anyway.

[lbendlin · Accepted solution]

You add a custom column, and in its code specify 

=Web.Contents([URL])

This will then translate into an "each"  process that pulls the data from all the product pages. The column contents will read "binary" and will be clickable.  Think of it as tables inside table cells.

You can then encapsulate that with your JSON parser or whatever the format is.

[lbendlin · Accepted solution]

No need to delete anything - Power Query always starts from scratch.

The standard approach is

- have  a list of Product IDs

- construct the URL for each of them

- use Web.Contents() to fetch the data for each

- expand the result into additional columns

It will still have to call the API for each individual product which sounds cumbersome, but that seems to be your environment restriction anyway.

[Tommy____G]

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.

[lbendlin]

Don't use the functions.  Do all your Web.Contents calls inside the original table as a custom columns and then expand as needed. Make sure that your original authentication is against the base URL only.

Use a parameter to store the API Key.  Also look at the options settings for Web.Contents, it has a special section for API Key field names.

[Tommy____G]

Hi Ibendlin,

I'm very new to M Query (Old MS Access guy), so I'm not following you.

I have created a table with all the Product IDs in.

I have added a custom column and made a URL for every ProductID (Same URL, just a different ProductID in the middle of it.

How do I use the Web.Contents call against all the values in the table I just created without usinga function?

Thanks for your help... I'm in the UK and my brain is running low on power...lol

[lbendlin · Accepted solution]

You add a custom column, and in its code specify 

=Web.Contents([URL])

This will then translate into an "each"  process that pulls the data from all the product pages. The column contents will read "binary" and will be clickable.  Think of it as tables inside table cells.

You can then encapsulate that with your JSON parser or whatever the format is.

[Tommy____G · Accepted solution]

Thanks so much!  A nights sleep and this just made sense this morning, a whole new M query world has just opened up for me....  Have a great day Ibendlin!

[Syndicate_Admin]

Thanks for the direction, I'll see how I go with this approach.

[Tommy____G]

Thanks for the direction, I'll see how I go with this approach.