Dynamic Qurey

[mikey141]

I’ve been trying to find a way to change location and date from import web page and wondered if somebody could help please?

http://www.realtimetrains.co.uk/search/advanced/RAM/2018/09/26/0000-2359?stp=WVS&show=all&order=wtt

The two areas are highlighted in bold and “RAM” I was going to use a lookup field. The change of date

I am using Excel 2016

 

[DRSteele]

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:

Book1
	Q	R
1	Team	HR_Address
2	CGY	https://www.hockey-reference.com/teams/CGY/2019_games.html
3
Q_sched

Cell Formulas
Range	Formula
R2	="https://www.hockey-reference.com/teams/"&Q2&"/2019_games.html"

Step two: create a query called QText_HR_Sched and paste this as the M-code. Close and load it to a connection only.

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.

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