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
EndDate
Parameter	Value
AddressOne
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>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #5B9BD5"]StartDate[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7"]2017-01-02[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7"]2017-01-17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #DDEBF7"]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[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G9[/TH]
[TD="align: left"]="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"[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G10[/TH]
[TD="align: left"]="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"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.