Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.


Well-known Member
Mar 31, 2015
Office Version
  1. 365
  1. Windows
I am no expert in this field so I simply will demonstrate how to parametrise a query. I hope this is on point here.

I suppose you are trying to use Power Query with a web address that resides in your spreadsheet. You would like to somehow alter a cell in the spreadsheet and have PQ use that new web address as the parameter. If that is so, here is my example you can try out. It completes a query to download the schedule for an NHL team conditional upon what team's name is in the Table.

Step one: I created a Table named T_SchedTeam. The first cell has a data validation for each of the NHL's 31 team names (but you can just type in things like CGY or EDM or VAN for purposes of our experiment). The adjacent cell has a concatenated formula that reports the web address. Like this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Team</td><td style=";">HR_Address</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;text-align: right;color: #C00000;background-color: #DDEBF7;;">CGY</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Q_sched</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">R2</th><td style="text-align:left">=""&Q2&"/2019_games.html"</td></tr></tbody></table></td></tr></table><br />

Step two: create a query called QText_HR_Sched and paste this as the M-code. Close and load it to a connection only.
    Source = Excel.CurrentWorkbook(){[Name="T_SchedTeam"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"HR_Address", type text}}),
    SchedTeam = Record.Field(Source{0},"HR_Address")

Step three: create a query called Q_SchedTable and paste this as the M-code.
    Source = Web.Page(Web.Contents(Text.From(QText_HR_Sched))),
    Data1 = Source{1}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data1,{"GF", "GA", "2", "3", "W", "L", "OL", "Streak", "Att.", "LOG", "Notes"})
    #"Removed Columns"

Step four: Select 'Close and Load to...' and put it in your spreadsheet. Change CGY to EDM, refresh the queries and see how the parameter feeds into the other query.

This should show the schedule for the team you've selected in the parameter table from step one.
Last edited:

Watch MrExcel Video

Forum statistics

Latest member