Rest API and Product Table Loop

[mattramirez2020]

Hello! I finally have a working query connected to my product table Rest API. The API can only get one item at a time and I need it to loop through variations up to a certain number. I am afraid to have it stop after a failure because sometimes the product numbers skip. In the screenshot below it starts at product 25315 and I would want the loop to start there and subsequently add one until let's just say product 26000. I am really overwhelmed and not really sure where to start and any help would be so appreciated!

In theory, it would gather the data from the url: https://api.fakeurl.com/1/product/left/25315 then https://api.fakeurl.com/1/product/left/25316  then https://api.fakeurl.com/1/product/left/25317 all the way through to 26000 and have everything under the same consistent headers.

QUERY:

let
actualUrl = "https://api.fakeurl.com/1/product/left/25315",
options = [Headers =[#"Authorization"="Basic ABC123AxABCoABCabABa"]],
result = Json.Document(Web.Contents(actualUrl, options)),
#"Converted to Table" = Record.ToTable(result),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"manufacturer", type text}, {"type", type text}, {"model", type text}})
in
#"Changed Type" 

[parry2k]

@mattramirez2020 if something is repeating, I would create one single table and pass the left and right values to the function and then let the function take care of it. 

[mattramirez2020]

@parry2k I might be an idiot but I am totally confused! Does it matter that the ID numbers are not unique and there can be a left/30244 left and then a right/30244 and they are totally different products? Are you suggesting duplicating my queries and functions and then just merging at the end or would I actually be able to create multiple ranges of product IDs in the main query itself? Is there anything I can do to my existing query to have two things pushed through with independent ranged for each type?

Ideal table output:

Purchase_Type Product_ID manufacturer type model

left 30244 Contoso A Blue

left 30245 Contoso B Red

right 19023 Contoso MM Orange

right 19024 Contoso MM Yellow

other 106 Contoso Q Purple

other 107 Contoso Q Red

accessory 453 Contoso R Purple

accessory 541 Contoso R Red

[mattramirez2020]

@parry2k So I have a follow up question! At the API url, I need to now source multiple ranges of data. For my example, I used https://api.fakeurl.com/1/product/left/25315 with 25315 being replaced by a range (example being source = {30244..30444}). What I actually need to is source "left" products, "right" product, "other" products and "accessory" products. Would it be better to basically replicate your queries and functions four times over or is there a good way to maybe combine them so that all items actually get agregated into one main table? Each product type technically needs its own range so left might use 30244...30444, right might use 19023..19029, other might use 106..195, and accessory might use 453..499. The only difference is actually now that instead of just the ID changing, the ID and the word "left" in the URL would change as well. I hope I am not explaining this poorly but any advice would be so appreciated!!!

https://api.fakeurl.com/1/product/<purchase_type>/<product ID>

[parry2k · Accepted solution]

@mattramirez2020 I create a simple pbix file with a function, in the advanced editor add your API Key and it should work. I hope this will get you started.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mattramirez2020]

Wow! Thank you @parry2k It worked perfectly!!!!