Forum Discussion
API returns 1 Product. I need to loop through and return all products into 1 table
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!
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.
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
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!