The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
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.
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
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?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |