Parameter in PQ for dates in web address

[DRSteele]

Can someone please help me?

I wish to have an Excel workbook where I can type two dates that will become the parameters for a PowerQuery process, the first one could be called StartDate and the second one Enddate. The large, bold text below is what I wish to parameterize.

The web address is (with superfluous spaces added by me so that the editor won't turn it into a hyperlink):

http:// www. bankofcanada.ca/rates/exchange/10-year-converter/?lookupPage=lookup_currency_converter.php&startRange=2007-01-18&selectToFrom=to&convert=1.00&seriesFrom=LOOKUPS_CAD&seriesTo%5B%5D=LOOKUPS_IEXE0101&rangeType=dates&dFrom=2017-01-03&dTo=2017-01-17&rangeValue=1&submit_button=Convert

 

[MarcelBeug]

Recently I created a short video instruction for a similar question to select records from a table based on parameters:
- create a parameter table with parameter names and values
- create a connection to this table with Power Query
- use this table to create a function that retrieves the parameter value from the parameter table for a specific parameter name

The first 2 minutes of the video are relevant for you.

In your query you can use it like:
"http:// www. bankofcanada.ca/rates/exchange/10-year-converter/?lookupPage=lookup_currency_converter.php&startRange=2007-01-18&selectToFrom=to&convert=1.00&seriesFrom=LOOKUPS_CAD&seriesTo%5B%5D=LOOKUPS_IEXE0101&rangeType=dates&dFrom=" & ParameterValue("StartDate") & "&dTo=" & ParameterValue("EndDate") & "&rangeValue=1&submit_button=Convert"

 

[DRSteele]

Thanks for your reply.

I'm having no luck trying to incorporate your the advice in your video. When I get to the point marked by 1:25 mark of your video and select Done, I get something different - it asks to Enter the Parameter, with choices of Invoke and Clear.

I also figured it would be easier to have the web address controlled within the spreadsheet itself, and so I set this up. There are three Tables: Starts, Ends and Parameters. With this in mind, I just want to use one of the two Addresses as the Source for the web query. Can this be done?

	E	F	G	H
1		StartDate
2		2017-01-02
3
4		EndDate
5		2017-01-17
6
7
8		Parameter	Value
9		AddressOne	http://www.bankofcanada.ca/rates/exchange/10-year-converter/?lookupPage=lookup_currency_converter.php&startRange=2007-01-18&selectToFrom=to&convert=1.00&seriesFrom=LOOKUPS_CAD&seriesTo%5B%5D=LOOKUPS_IEXE0101&rangeType=dates&dFrom=2017-01-02&dTo=2017-01-17&rangeValue=1&submit_button=Convert
10		AddressTwo	http://www.bankofcanada.ca/rates/exchange/10-year-converter/?lookupPage=lookup_currency_converter.php&startRange=2007-01-18&selectToFrom=to&convert=1.00&seriesFrom=LOOKUPS_EUROCAE01&seriesTo%5B%5D=LOOKUPS_IEXE0101&rangeType=dates&dFrom=2017-01-02&dTo=2017-01-17&rangeValue=1&submit_button=Convert

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​

Worksheet Formulas Cell Formula G9 ="http://www.bankofcanada.ca/rates/exchange/10-year-converter/?lookupPage=lookup_currency_converter.php&startRange=2007-01-18&selectToFrom=to&convert=1.00&seriesFrom=LOOKUPS_CAD&seriesTo%5B%5D=LOOKUPS_IEXE0101&rangeType=dates&dFrom="&Starts[StartDate]&"&dTo="&Ends[EndDate]&"&rangeValue=1&submit_button=Convert" G10 ="http://www.bankofcanada.ca/rates/exchange/10-year-converter/?lookupPage=lookup_currency_converter.php&startRange=2007-01-18&selectToFrom=to&convert=1.00&seriesFrom=LOOKUPS_EUROCAE01&seriesTo%5B%5D=LOOKUPS_IEXE0101&rangeType=dates&dFrom="&Starts[StartDate]&"&dTo="&Ends[EndDate]&"&rangeValue=1&submit_button=Convert" <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[MarcelBeug]

My original approach doesn't work due to Formula.Firewall errors when accessing a web page based on output from another query.

But this alternative works:
1. Add parameter "UseAddress" to the parameter table with value either "AddressOne" or "AddressTwo" (you can use data validation for a list).
2. Create a query based on the "Parameters" table that first retrieves the "UseAddress" value, then retrieves the corresponding address and gets the web contents from that address.

This query works (but I don't know if I drilled down to the right part of the web address):

let
    Source = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", type text}, {"Value", type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Parameter] = "UseAddress")),
    Value = #"Filtered Rows1"{0}[Value],
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Parameter] = Value)),
    WebContents = Web.Page(Web.Contents(#"Filtered Rows2"{0}[Value])),
    Data = WebContents{1}[Data],
    #"Changed Type1" = Table.TransformColumnTypes(Data,{{"Date", type date}, {"USD = U.S. dollar (noon)", type text}, {"Exchange rate", type text}})
in
    #"Changed Type1"

 

[DRSteele]

Marcel, thanks tons for your help.

Sorry for what looks like a late reply. The forum has had trouble recently.

I was able to get most of this working. I will keep pressing ahead with these ideas.