Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Connecting to the NBA stats API

Hi everyone,   I am relatively new to connecting to public APIs and I have a problem. I'm trying to connect to the NBA API for draft history across all years and teams using the following GET: http...
Need Help
dearwatson's avatar
dearwatson
Continued Contributor
7 years ago

Hi pops,

 

I messed around and got this query to extract the data...

 

let
    Source = Web.BrowserContents("https://stats.nba.com/stats/drafthistory?LeagueID=00"),
    #"Imported Text" = Lines.FromText(Source),
    #"Replaced Value" = List.ReplaceValue(#"Imported Text","<html><head></head><body><pre style=""word-wrap: break-word; white-space: pre-wrap;"">","",Replacer.ReplaceText),
    #"Replaced Value1" = List.ReplaceValue(#"Replaced Value","</pre></body></html>","",Replacer.ReplaceText),
    #"Converted to Table" = Table.FromList(#"Replaced Value1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Parsed JSON" = Table.TransformColumns(#"Converted to Table",{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"resource", "parameters", "resultSets"}, {"resource", "parameters", "resultSets"}),
    resultSets = #"Expanded Column1"{0}[resultSets],
    resultSets1 = resultSets{0},
    #"Converted to Table1" = Record.ToTable(resultSets1),
    Value = #"Converted to Table1"{2}[Value],
    #"Converted to Table2" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table2", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", type text}, {"Column1.3", Int64.Type}, {"Column1.4", Int64.Type}, {"Column1.5", Int64.Type}, {"Column1.6", Int64.Type}, {"Column1.7", type text}, {"Column1.8", Int64.Type}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}})
in
    #"Changed Type"

its a bit messy but it works :)

 

not sure why it won't parse the page natively so I pulled it using the web.browsercontents and took out the html tags.