Dynamic Connection to Web URL containing YYYY-YY

[john121]

My Url link - http://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html

I need to paas 2 parameters both which are year , first parameter in format yyyy and second in yy.

I'm having issue how to pass the second parameter.

(year,yr as number)=>

let
Source = Csv.Document(Web.Contents("http://www.quanthockey.com/khl/seasons/" & ""&Number.ToText(year)&"" & "-" & ""&Number.ToText(year)&"" & ".csv"),[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text},
{"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}})
in
#"Changed Type"

The above query is not running correctly for me.

Let me know what is the issue.

Thanks for the help

[v-jingzhang · Accepted solution]

Hi @john121 

The url in your custom function is not valid. It cannot access such a csv file. 

If you want to get data from the html table in this url link - http://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html, you can use the Web connector directly. Go to New source > Web, enter above url link and select Anonymous for authentication. In Navigator window, select the html table that has all data. 

This is the code generated automatically. 

let
    Source = Web.BrowserContents("https://www.quanthockey.com/khl/seasons/2017-18-khl-players-stats.html"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='statistics'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='statistics'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='statistics'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='statistics'] > * > TR > :nth-child(4)"}, {"Column5", "TABLE[id='statistics'] > * > TR > :nth-child(5)"}, {"Column6", "TABLE[id='statistics'] > * > TR > :nth-child(6)"}, {"Column7", "TABLE[id='statistics'] > * > TR > :nth-child(7)"}, {"Column8", "TABLE[id='statistics'] > * > TR > :nth-child(8)"}, {"Column9", "TABLE[id='statistics'] > * > TR > :nth-child(9)"}, {"Column10", "TABLE[id='statistics'] > * > TR > :nth-child(10)"}, {"Column11", "TABLE[id='statistics'] > * > TR > :nth-child(11)"}, {"Column12", "TABLE[id='statistics'] > * > TR > :nth-child(12)"}, {"Column13", "TABLE[id='statistics'] > * > TR > :nth-child(13)"}, {"Column14", "TABLE[id='statistics'] > * > TR > :nth-child(14)"}, {"Column15", "TABLE[id='statistics'] > * > TR > :nth-child(15)"}, {"Column16", "TABLE[id='statistics'] > * > TR > :nth-child(16)"}, {"Column17", "TABLE[id='statistics'] > * > TR > :nth-child(17)"}}, [RowSelector="TABLE[id='statistics'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rk", Int64.Type}, {"", type text}, {"Name", type text}, {"Age", Int64.Type}, {"Pos", type text}, {"GP", Int64.Type}, {"G", Int64.Type}, {"A", Int64.Type}, {"P", Int64.Type}, {"PIM", Int64.Type}, {"+/-", Int64.Type}, {"PPG", Int64.Type}, {"SHG", Int64.Type}, {"GWG", Int64.Type}, {"G/GP", type number}, {"A/GP", type number}, {"P/GP", type number}})
in
    #"Changed Type"

To convert it into a custom function with dynamic year parameters, you can use below code

(year, yr as number)=>
let
    Source = Web.BrowserContents("https://www.quanthockey.com/khl/seasons/"&Number.ToText(year)&"-"&Number.ToText(yr)&"-khl-players-stats.html"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='statistics'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='statistics'] > * > TR > :nth-child(2)"}, {"Column3", "TABLE[id='statistics'] > * > TR > :nth-child(3)"}, {"Column4", "TABLE[id='statistics'] > * > TR > :nth-child(4)"}, {"Column5", "TABLE[id='statistics'] > * > TR > :nth-child(5)"}, {"Column6", "TABLE[id='statistics'] > * > TR > :nth-child(6)"}, {"Column7", "TABLE[id='statistics'] > * > TR > :nth-child(7)"}, {"Column8", "TABLE[id='statistics'] > * > TR > :nth-child(8)"}, {"Column9", "TABLE[id='statistics'] > * > TR > :nth-child(9)"}, {"Column10", "TABLE[id='statistics'] > * > TR > :nth-child(10)"}, {"Column11", "TABLE[id='statistics'] > * > TR > :nth-child(11)"}, {"Column12", "TABLE[id='statistics'] > * > TR > :nth-child(12)"}, {"Column13", "TABLE[id='statistics'] > * > TR > :nth-child(13)"}, {"Column14", "TABLE[id='statistics'] > * > TR > :nth-child(14)"}, {"Column15", "TABLE[id='statistics'] > * > TR > :nth-child(15)"}, {"Column16", "TABLE[id='statistics'] > * > TR > :nth-child(16)"}, {"Column17", "TABLE[id='statistics'] > * > TR > :nth-child(17)"}}, [RowSelector="TABLE[id='statistics'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rk", Int64.Type}, {"", type text}, {"Name", type text}, {"Age", Int64.Type}, {"Pos", type text}, {"GP", Int64.Type}, {"G", Int64.Type}, {"A", Int64.Type}, {"P", Int64.Type}, {"PIM", Int64.Type}, {"+/-", Int64.Type}, {"PPG", Int64.Type}, {"SHG", Int64.Type}, {"GWG", Int64.Type}, {"G/GP", type number}, {"A/GP", type number}, {"P/GP", type number}})
in
    #"Changed Type"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@john121 , check this can help

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...