Parameter in PQ for dates in web address

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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?

EFGH
1StartDate
22017-01-02
3
4EndDate
52017-01-17
6
7
8ParameterValue
9AddressOnehttp://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
10AddressTwohttp://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
CellFormula
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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):
Code:
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,338
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,307
Messages
5,600,869
Members
414,411
Latest member
Snowmanaus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top