Forum Discussion
API returns 1 Product. I need to loop through and return all products into 1 table
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
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.
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.
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!
8 Replies
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.
Thanks for the direction, I'll see how I go with this approach.
Thanks for the direction, I'll see how I go with this approach.
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.
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.
