Forum Discussion
Dynamic Connection to Web URL containing YYYY-YY
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
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.
2 Replies
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.
