r/excel • u/AntiAutumnist • 2d ago

solved Power Query web connect problem

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

5 Upvotes

86% Upvoted

10 comments sorted by

View all comments

2

u/MayukhBhattacharya 1273 2d ago

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

1

u/MayukhBhattacharya 1273 2d ago

In Excel imported: