Make a date from Excel be the source for a Query

[DRSteele]

Can someone please help me?

I wish to have the date in my ExcelTable become the source for a PQ Query. I can then change the date in H2 and the query will get the standings for that date. How do I create the parameter and then use it as the source?

My Table is called T_NHL_Standings

	G	H	I
NHL_Date	NHL_Address
http://www1.nhl.com/ice/standings.htm?date=10/13/2017&season=20172018&type=lea

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10/13/2017[/TD]

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

</tbody>

Q_Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]="http://www1.nhl.com/ice/standings.htm?date="&TEXT(H2,"mm/dd/yyyy")&"&season=20172018&type=lea"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[MarcelBeug]

If you make H2 named range "NLH_Range", then you can create a parameter record "Prm" in Power Query with query "Prm":

let
    Source = Excel.CurrentWorkbook(){[Name="NHL_Date"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Name", each "NLH_Date"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Value"}}),
    RecordFromTable = Record.FromTable(#"Renamed Columns")
in
    RecordFromTable

You can then use the parameter in your query to get the data from the web:

Source = Web.Page(Web.Contents("http://www1.nhl.com/ice/standings.htm?date=" & Date.ToText(Prm[NLH_Date],"MM/dd/yyyy") & "season=20172018&type=lea")),

And proceed from there.

Note: when you get a Formula.Firewall error, you need to adjust the Privacy options to ignore the Privacy levels.

 

[DRSteele]

Marcel, thank you very much. It worked just fine with the exception that the named range should have been NHL_Date (not NLH_Range as you had mistyped). You were right that I had to adjust the privacy settings for the website being queried.

I was tinkering around and I found that I could also have used my original table (called T_NHL_Standings) by creating a text query and then passing that into the source line by using a text function - I chose Text.From and it seemed to work.

So I created a query called QText_NHL_Standings and loaded it as a connection only.

let
    Source = Excel.CurrentWorkbook(){[Name="T_NHL_Standings"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NHL_Date", type datetime}, {"NHL_Address", type text}}),
    NHL_Standings_Date = Record.Field(Source{0},"NHL_Address")
in
    NHL_Standings_Date

Then I created another query called Q_NHL_Standings and loaded it to the workbook.

let
    Source = Web.Page(Web.Contents(Text.From(QText_NHL_Standings))),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"", Int64.Type}, {"2", type text}, {"DIV", type text}, {"GP", Int64.Type}, {"W", Int64.Type}, {"L", Int64.Type}, {"OT", Int64.Type}, {"P", Int64.Type}, {"ROW", type text}, {"GF", Int64.Type}, {"GA", Int64.Type}, {"Diff", type text}, {"Home", type text}, {"Away", type text}, {"S/O", type text}, {"L10", type text}, {"Streak", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"", "Rank"}, {"2", "Team"}})
in
    #"Renamed Columns"

Now I can adjust that date and retrieve the standings very easily. Super! Thanks for the help.