Power Query: reading data from web and populating query url with parameters from table

[chilly_bang]

Hi
I'm using Power Query to read some data through a web API. The API is queried with an URL containing some paramaters and responses with data. The query URL looks like:

http://api.semrush.com/?type=url_organic&key=$key&display_limit=10&export_columns=Ph,Po,Ur&url=http://example.com/&database=de&display_filter=-Ph|Co|test

The workaround here is simple: Power Query → read data from web → input url → done


Now i want to populate the query url with parameters from an Excel table, so the query urls will look like:

"http://api.semrush.com/?type=url_organic&key=$key&display_limit=10&export_columns=Ph,Po,Ur&url="&[B]A2[/B]&"&database="&[B]B2[/B]&"&display_filter=-Ph|Co|test"

Could somebody point me to the correct workflow and syntax, so i reach my goal?

thanks and best regards

 

[Comfy]

Using your example of Parameters in A2:B2 and assuming headers in A1:B1

Turn this into a table and pull into Power Query (I have called the Table and resulting Query "tParam"):

Excel 2010
	A	B
1	url	database
2	http://example.com/	de
3
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="tParam"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"url", type text}, {"database", type text}})
in
    #"Changed Type"

You can then refer to these parameters in your main Query:

let
    dBase = tParam[database]{0},
    url = tParam{0},
    Source = "http://api.semru...lay_filter=-Ph|Co|test"
in
    Source
[/code]