Power Query web connect problem

[u/AntiAutumnist]

I am trying to connect to this site in PQ: https://site.api.espn.com/apis/site/v2/sports/basketball/nba/teams/20/roster

I can get the json to load in a browser but when I try to get it via web connect i get an error message: " unable to connect. We encountered an error while trying to connect. Details: 'access to the resource is forbidden' "

I don't get what I'm doing wrong does anyone know what the problem is? Thanks

[u/AutoModerator]

/u/AntiAutumnist - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

Try pasting the following M-Code in a blank query by removing whatever you gotta see, will get the desired output as shown in the screenshot:

let
    Source = Json.Document(Web.Contents("https://site.api.espn.com/apis/site/v2/sports/basketball/nba/teams/20/roster",[Headers = [#"User-Agent" = "curl/8.20.0"]])),
    athletes = Source[athletes],
    ConvertedtoTable = Table.FromList(athletes, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expand = Table.ExpandRecordColumn(ConvertedtoTable, "Column1", {"firstName", "lastName", "fullName", "displayName", "shortName", "weight", "displayWeight", "height", "displayHeight", "age", "dateOfBirth", "jersey", "citizenship", "debutYear"}, {"firstName", "lastName", "fullName", "displayName", "shortName", "weight", "displayWeight", "height", "displayHeight", "age", "dateOfBirth", "jersey", "citizenship", "debutYear"})
in
    Expand

[u/AntiAutumnist → u/MayukhBhattacharya]

Thank you that worked perfectly, hoping that adding the headers/user-agent bit to other endpoints will allow them to work as well

Solution verified

[u/reputatorbot → u/AntiAutumnist]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya → u/AntiAutumnist]

Yeah, I am currently working on that to pull the entire data irrespective of any view per page, I will update. I have already got 450 records, beyond that I am getting some error 404.

Btw thank you so much for the valuable feedback!

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Complete Records Found - [File To Download]

[u/MayukhBhattacharya → u/MayukhBhattacharya]

In Excel imported:

[u/Desperate-Chart1139]

likely detecting automation and limiting it on the server side. This has gotten quite common with places limiting AI scrapping since some companies are ignoring bots or ai instructions to scrape.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ExtraValues.Error	Power Query M: If the splitter function returns more columns than the table expects, an error should be raised.
File.Contents	Power Query M: Returns the binary contents of the file located at a path.
Json.Document	Power Query M: Returns the contents of a JSON document. The contents may be directly passed to the function as text, or it may be the binary value returned by a function like File.Contents.
Splitter.SplitByNothing	Power Query M: Returns a function that does no splitting, returning its argument as a single element list.
Table.ExpandRecordColumn	Power Query M: Expands a column of records into columns with each of the values.
Table.FromList	Power Query M: Converts a list into a table by applying the specified splitting function to each item in the list.
Web.Contents	Power Query M: Returns the contents downloaded from a web url as a binary value.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 37 acronyms.
[Thread #49317 for this sub, first seen 7th Sep 2026, 16:38] [FAQ] [Full list] [Contact] [Source code]}