How do I pull this website data?

[srallen3]

I am trying to pull these NBA statistics from this webiste: 2020-21 NBA Team Opponent Stats | ESPN.

My current formula is: =IMPORTHTML("2020-21 NBA Team Opponent Stats | ESPN","table",1)


However this only pulls the Team Rank and Team Name columns, I have tried editing the formula but I cannot get it to pull the rest of the data to the right of the teams. Any thoughts/ideas?

 

[srallen3]

so use Power Query

// Table 0
let
    Source = Web.Page(Web.Contents("https://www.espn.com/nba/stats/team/_/view/opponent")),
    Data0 = Source{0}[Data],
    Index = Table.AddIndexColumn(Data0, "Index", 1, 1)
in
    Index

// Table 1
let
    Source = Web.Page(Web.Contents("https://www.espn.com/nba/stats/team/_/view/opponent")),
    Data1 = Source{1}[Data],
    Index = Table.AddIndexColumn(Data1, "Index", 1, 1)
in
    Index

// Merge1
let
    Source = Table.NestedJoin(#"Table 0",{"Index"},#"Table 1",{"Index"},"Table 1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Source, "Table 1", {"GP", "PTS", "FGM", "FGA", "FG%", "3PM", "3PA", "3P%", "FTM", "FTA", "FT%", "OR", "DR", "REB", "AST", "STL", "BLK", "TO", "PF"}, {"GP", "PTS", "FGM", "FGA", "FG%", "3PM", "3PA", "3P%", "FTM", "FTA", "FT%", "OR", "DR", "REB", "AST", "STL", "BLK", "TO", "PF"}),
    RC = Table.RemoveColumns(Expand,{"Index"}),
    Type = Table.TransformColumnTypes(RC,{{"RK", Int64.Type}, {"Team", type text}, {"GP", Int64.Type}, {"PTS", type number}, {"FGM", type number}, {"FGA", type number}, {"FG%", type number}, {"3PM", type number}, {"3PA", type number}, {"3P%", type number}, {"FTM", type number}, {"FTA", type number}, {"FT%", type number}, {"OR", type number}, {"DR", type number}, {"REB", type number}, {"AST", type number}, {"STL", type number}, {"BLK", type number}, {"TO", type number}, {"PF", type number}})
in
    Type

just in case

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

Got it, thank you!

 

[sandy666]

You are welcome & have a nice day

 

[srallen3]

You are welcome & have a nice day

I have another question if you don't mind, if so just let me know and I will post another thread.

I decided to also add an entire excel database tab of every current NBA player that has a column for their respective team, and then columns for stats. Is there a formula I can input that will search that database tab for a specific team, and then return the player name beside that team to that cell, and then do the same thing for the cell under it but the next players name, and so on and so forth until every player that plays for that team has had their name returned?

 

[sandy666]

This is different question so post new thread
(with representative data source and expected result and detailed description)