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: https://stats.nba.com/stats/drafthistory?LeagueID=00

 

I'm not sure how to query this using the Advanced Editor in Power BI. So far, I've tried the following to no success:

---------------------------------------------------------------------------------------------------------------------------

let
Source = {"https://stats.nba.com/stats/drafthistory?LeagueID=00"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://stats.nba.com/stats/drafthistory?LeagueID=00")))
in
#"Added Custom"

---------------------------------------------------------------------------------------------------------------------------

 

Does anyone have any ideas on how to create rows and columns from the API? Any help is much appreciated! 

Need Help

3 Replies

  • dearwatson's avatar
    dearwatson
    Continued Contributor
    7 years ago

    Hi pops562,

     

    To query the NBA stats API use the Web.Contents function in Power Query, you can do this through the UI by selecting "Get Data" -> "Web" and use either basic mode (1 page) or advanced mode if you want a paginated query.

     

    There is a good example of this technique from Reza here: https://radacad.com/get-started-with-power-query-movies-data-mash-up 

     

    It looks like you will need to transform it from JSON. 

     

    I'll post a sample if I get a sec.

     

    Cheers

    Greg

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Greg,

       

      I tried putting the URL for the API into the "Get Web" option, but the query timed out and I wasn't able to get it. Any advice?

  • 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.