Passing Variable in URL (Power Query)- Loop vs Parameter

[goob90]

Hello everyone, and happy almost Thanksgiving!

I am working on a project in Power Query and need advice as to how I should pass a variable in my URL. The URL is for free access to an API that allows 100 combined request/day.

The original URL has a spot where a UPC code can be changed to lookup an item:

https://api.upcitemdb.com/prod/trial/lookup?upc=

5010993689071

Currently, I have a list using Textjoin and I have gotten it to work somewhat. However, the URL I am using will only return two values at a time. So anything past the first two UPC codes does not get returned.

let
    List=Excel.CurrentWorkbook(){[Name="UPCList"]}[Content]{0}[Column1],
    Source = Json.Document(Web.Contents("https://api.upcitemdb.com/prod/trial/lookup?upc="&List&" ")),
    items = Source[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"ean", "title", "description", "brand", "model", "color", "size", "dimension", "weight", "category", "currency", "lowest_recorded_price", "highest_recorded_price", "images", "offers", "elid"}, {"Column1.ean", "Column1.title", "Column1.description", "Column1.brand", "Column1.model", "Column1.color", "Column1.size", "Column1.dimension", "Column1.weight", "Column1.category", "Column1.currency", "Column1.lowest_recorded_price", "Column1.highest_recorded_price", "Column1.images", "Column1.offers", "Column1.elid"})
in
    #"Expanded Column1"

Would I be better off running some sort of loop that goes through the list one by one? If so, how would I do that? I am open to other solutions as well.

Thank you all so much!

 

[lrobbo314]

If you create a table of your UPC codes and load it into PQ, you can then add a column to that table with a function to get the info.

Custom function

(upc as text) as table => 
let
    Source = Json.Document(Web.Contents("https://api.upcitemdb.com/prod/trial/lookup?upc=" & upc)),
    items = Source[items],
    items1 = items{0},
    toTable = Record.ToTable(items1)
in
    toTable

UPC List

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"APIs", type text}}),
    UPC = Table.AddColumn(Type, "Custom", each FN([APIs])),
    Expand = Table.ExpandTableColumn(UPC, "Custom", {"Name", "Value"}, {"Name", "Value"})
in
    Expand

New__Document (8).xlsx
	A	B	C
2	5010993689071	ean	5010993689071
3	5010993689071	title	Hasbro - Ghostbusters Plasma Series Winston Zeddemore Action Figure
4	5010993689071	description	Includes figure and 3 accessories
5	5010993689071	brand	Hasbro
6	5010993689071	model	78325700
7	5010993689071	color	silver,grey
8	5010993689071	size
9	5010993689071	dimension
10	5010993689071	weight	1.25 lbs
11	5010993689071	category	Toys & Games > Toys > Dolls, Playsets & Toy Figures > Action & Toy Figures
12	5010993689071	currency
13	5010993689071	lowest_recorded_price	15.97
14	5010993689071	highest_recorded_price	155
15	5010993689071	images
16	5010993689071	offers
17	5010993689071	elid	265386804334
18	653569732259	ean	0653569732259
19	653569732259	title	Twister
20	653569732259	description	This Twister game is the classic game with 2 more moves.
21	653569732259	upc	653569732259
22	653569732259	brand	Hasbro
23	653569732259	model	4645
24	653569732259	color	Black
25	653569732259	size
26	653569732259	dimension	10 X 10 inches
27	653569732259	weight	0.00 lb
28	653569732259	category	Toys & Games > Games > Board Games
29	653569732259	currency	CAD
30	653569732259	lowest_recorded_price	0
31	653569732259	highest_recorded_price	60.55
32	653569732259	images
33	653569732259	offers
34	653569732259	asin	B008J87PVC
35	653569732259	elid	194456117411
Sheet4

 

[goob90]

Thank you! That worked pretty well.

Is there a way to transpose the multiple tables at once? I'd like to have fields as headers with the product data below them.

 

[lrobbo314]

Like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	UPC	title	description	brand	model	color	size	dimension	weight	category	currency	lowest_recorded_price	highest_recorded_price	images	offers	elid	upc.1	asin
2	5010993689071	Hasbro - Ghostbusters Plasma Series Winston Zeddemore Action Figure	Includes figure and 3 accessories	Hasbro	78325700	silver,grey			1.25 lbs	Toys & Games > Toys > Dolls, Playsets & Toy Figures > Action & Toy Figures		15.97	155			265386804334
3	653569732259	Twister	This Twister game is the classic game with 2 more moves. Give the spinner a whirl and see what's next as you try to keep your hands and feet on the mat! Right foot red! Can you do it? Left foot green, you got it! If your knee or elbow touches the mat, or you fall over, you're out.	Hasbro	4645	Black		10 X 10 inches	0.00 lb	Toys & Games > Games > Board Games	CAD	0	60.55			194456117411	653569732259	B008J87PVC
Table1

Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"UPC", type text}}),
    UPC = Table.AddColumn(Type, "Custom", each FN([UPC])),
    Expand = Table.ExpandRecordColumn(UPC, "Custom", {"title", "description", "brand", "model", "color", "size", "dimension", "weight", "category", "currency", "lowest_recorded_price", "highest_recorded_price", "images", "offers", "elid", "upc", "asin"}, {"title", "description", "brand", "model", "color", "size", "dimension", "weight", "category", "currency", "lowest_recorded_price", "highest_recorded_price", "images", "offers", "elid", "upc.1", "asin"})
in
    Expand

Function

(upc as text) as record => 
let
    Source = Json.Document(Web.Contents("https://api.upcitemdb.com/prod/trial/lookup?upc=" & upc)),
    items = Source[items],
    items1 = items{0}
in
    items1

 

[goob90]

Like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	UPC	title	description	brand	model	color	size	dimension	weight	category	currency	lowest_recorded_price	highest_recorded_price	images	offers	elid	upc.1	asin
2	5010993689071	Hasbro - Ghostbusters Plasma Series Winston Zeddemore Action Figure	Includes figure and 3 accessories	Hasbro	78325700	silver,grey			1.25 lbs	Toys & Games > Toys > Dolls, Playsets & Toy Figures > Action & Toy Figures		15.97	155			265386804334
3	653569732259	Twister	This Twister game is the classic game with 2 more moves. Give the spinner a whirl and see what's next as you try to keep your hands and feet on the mat! Right foot red! Can you do it? Left foot green, you got it! If your knee or elbow touches the mat, or you fall over, you're out.	Hasbro	4645	Black		10 X 10 inches	0.00 lb	Toys & Games > Games > Board Games	CAD	0	60.55			194456117411	653569732259	B008J87PVC
Table1

Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"UPC", type text}}),
    UPC = Table.AddColumn(Type, "Custom", each FN([UPC])),
    Expand = Table.ExpandRecordColumn(UPC, "Custom", {"title", "description", "brand", "model", "color", "size", "dimension", "weight", "category", "currency", "lowest_recorded_price", "highest_recorded_price", "images", "offers", "elid", "upc", "asin"}, {"title", "description", "brand", "model", "color", "size", "dimension", "weight", "category", "currency", "lowest_recorded_price", "highest_recorded_price", "images", "offers", "elid", "upc.1", "asin"})
in
    Expand

Function

(upc as text) as record =>
let
    Source = Json.Document(Web.Contents("https://api.upcitemdb.com/prod/trial/lookup?upc=" & upc)),
    items = Source[items],
    items1 = items{0}
in
    items1

Perfect! Thank you so much!

 

[Fluff]

@Cuneyt please start a thread of your own for this question. If you wish to post using your own language then it must be posted in the Questions in Other Languages section of the board.
Thanks