Passing a variable to Power Query

[127bit]

The following Power Query successfully downloads a table of statistics for NHL player Auston Matthews through the Get Data From Web function:

= Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=25238"))

'25238' is his primary record number. I would like to pass the primary record number as a variable. For example, change it to '23497' which would instead download the statistics of Connor McDavid, #23497. Placing "23497" into cell 'A3' and executing the following Power Query was unsuccessful:

= Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=(=a3)"))

Can you tell me what syntax/commands would perform this?
Thank-you.

 

[alansidman]

Here is a video from Mr E on how to build a parameter query

 

[smozgur]

A worksheet cell cannot be accessed directly in Power Query by using the cell address like "A3". However, it is perfectly possible to refer to a named range.

Name the A3 cell as "playerId" and then use the following code.
The first line defined with the identifier id retrieves the value of the named range "playerId" value (A3), and then you can use this id identifier to build the dynamic URL in the second line.

let
  id = Excel.CurrentWorkbook(){[Name="playerId"]}[Content]{0}[Column1],
  Source = Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=" & Text.From(id)))
in
  Source

 

[127bit]

A worksheet cell cannot be accessed directly in Power Query by using the cell address like "A3". However, it is perfectly possible to refer to a named range.

Name the A3 cell as "playerId" and then use the following code.
The first line defined with the identifier id retrieves the value of the named range "playerId" value (A3), and then you can use this id identifier to build the dynamic URL in the second line.

let
  id = Excel.CurrentWorkbook(){[Name="playerId"]}[Content]{0}[Column1],
  Source = Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=" & Text.From(id)))
in
  Source

smozgur - thanks for your reply.
Table 0 downloaded by the query I generated contains 9 rows of 48 data columns; here is that code with most of the columns removed to save space:

let
    Source = Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=25238")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Season", type text}, {"Team", type text}, ...
             * columns removed *
        ...{"Faceoffs FO%", Percentage.Type}})
in
    #"Changed Type"

I can't figure out precisely where to replace the generated code with yours, please describe further.
Thanks very much

 

[smozgur]

Select A3, click on the name box, and set the name as playerId as shown below.

Then your code should start like the following including the Source step.

let
    id = Excel.CurrentWorkbook(){[Name="playerId"]}[Content]{0}[Column1],
    Source = Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=" & Text.From(id)))
    // The rest of your code

 

[127bit]

Select A3, click on the name box, and set the name as playerId as shown below.

View attachment 107197

Then your code should start like the following including the Source step.

let
    id = Excel.CurrentWorkbook(){[Name="playerId"]}[Content]{0}[Column1],
    Source = Web.Page(Web.Contents("https://www.quanthockey.com/hockey-stats-nhl/en/profile.php?player=" & Text.From(id)))
    // The rest of your code

Got it. Thanks very much for your help.

 

[smozgur]

Got it. Thanks very much for your help.

You're welcome.
Thanks for the feedback.