Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,311
Office Version
  1. 365
Platform
  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=";">https://www.hockey-reference.com/teams/CGY/2019_games.html</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">="https://www.hockey-reference.com/teams/"&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.
Code:
let
    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")
in
    SchedTeam

Step three: create a query called Q_SchedTable and paste this as the M-code.
Code:
let
    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"})
in
    #"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

Threads
1,114,645
Messages
5,549,163
Members
410,902
Latest member
G Slim
Top