Referencing a cell value as a parameter in the URL in Excel's Data From Web function

Question

Hi I'm trying to get some data from a website into a worksheet using Excel's Data -> From Web function. But I want the parameter of the url to be dynamic and reference that value from a cell in the worksheet. I'm attaching a screenshot too. Could someone tell me how to reference the cell value under URL parts? Any help would be much appreciated. Excel screenshot

Answer 1

Name the cell with the parameter - say call it URLParameter. Then set up your query with a static value, open the editor and switch to the advanced editor. After the initial Let add a new step that looks like:

URLPart = Excel.CurrentWorkbook(){[Name="URLParameter"]}[Content]{0}[Column1],

Then amend the Web.Contents part of your source to add in the parameter - for example:

Web.Contents("www.blah.com/" & URLPart)