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?

 

[Bob Todd]

Download and install Power Query from Microsoft

Download Power Query from Official Microsoft Download Center

Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. (Note: The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. Please upgrade...

www.microsoft.com

 

[srallen3]

Download and install Power Query from Microsoft

Download Power Query from Official Microsoft Download Center

Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. (Note: The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. Please upgrade...

www.microsoft.com

Is that the only way or just the easiest way?

 

[sandy666]

first update your profile about Excel version and OS

 

[Bob Todd]

Sandy's correct, It would help to know what version you are using.

 

[srallen3]

Sandy's correct, It would help to know what version you are using.

Just updated. Office 2019. Just weird that I can pull tables/data on other sites no issues. Guess the second part of this table with the game statistics are implemented differently.

 

[sandy666]

is that what you want?

RK	Team	GP	PTS	FGM	FGA	FG%	3PM	3PA	3P%	FTM	FTA	FT%	OR	DR	REB	AST	STL	BLK	TO	PF
1	Phoenix Suns	6	98.7	37	82.2	45	8.5	28.3	30	16.2	22.8	70.8	8.2	35	43.2	20	6.3	3.3	14	20.3
2	New Orleans Pelicans	5	99.2	36.2	83.4	43.4	15	42.8	35	11.8	15.4	76.6	6.8	33.2	40	25.8	7.8	4.8	16.2	21.6
3	Philadelphia 76ers	5	99.8	36.8	88.8	41.4	10.8	30.4	35.5	15.4	19.4	79.4	9.8	34.8	44.6	23.2	8	5.2	15.8	22.8
4	Dallas Mavericks	5	103.6	37.4	81.6	45.8	11.2	33.8	33.1	17.6	23.4	75.2	9	36.6	45.6	23.4	6.8	4.2	16	24
5	Toronto Raptors	4	103.8	38.3	88	43.5	11	35.8	30.8	16.3	22.3	73	10	40	50	27	8.3	5.8	18.3	18.3
6	New York Knicks	5	105.2	38.8	89.6	43.3	10	37.4	26.7	17.6	24.6	71.5	12.4	35	47.4	23	8.4	7.2	13	20.6
7	Utah Jazz	5	106.2	39.2	89.6	43.8	14	33.2	42.2	13.8	17.6	78.4	8.4	31.8	40.2	22	7	5.4	10.4	19
8	Indiana Pacers	5	107	38	83.4	45.6	11.6	27	43	19.4	26.6	72.9	9.6	32	41.6	22.4	8.6	6.2	17.4	19.6
9	Los Angeles Lakers	6	107.8	40.3	90.5	44.6	12	35.2	34.1	15.2	20.5	74	8.8	31.8	40.7	23.5	9	5	12.3	21.5
10	Cleveland Cavaliers	5	108.2	38.8	86.6	44.8	14.8	37.2	39.8	15.8	22.8	69.3	9.8	35.4	45.2	25.6	10	4.2	19.4	19.8
10	Charlotte Hornets	5	108.2	39.6	86.8	45.6	13.4	38.8	34.5	15.6	22.4	69.6	9.6	37.8	47.4	28	9.2	5.4	16.4	18.8
12	LA Clippers	6	108.8	38	83.8	45.3	12.3	37.3	33	20.5	25.7	79.9	9.7	33.2	42.8	22.7	6	3	13.5	17.7
13	Milwaukee Bucks	6	110.5	40.5	88.8	45.6	12.7	35.7	35.5	16.8	21.7	77.7	7.8	33.8	41.7	21.7	7	5.5	15.8	19.8
14	Boston Celtics	6	111	41.8	86.5	48.4	11.3	31.7	35.8	16	20.8	76.8	9.8	33.8	43.7	22.3	7.7	5.5	16.7	19.3
15	Miami Heat	5	111.2	39.2	87.6	44.7	15.4	38.4	40.1	17.4	22.8	76.3	10.4	34.2	44.6	23.4	12	3.2	16.4	22.2
16	Brooklyn Nets	6	111.8	41.5	97.2	42.7	12	34.7	34.6	16.8	22.8	73.7	14.8	35.8	50.7	25.8	7.2	3.8	14.2	22
17	Oklahoma City Thunder	4	112	43.3	91.5	47.3	11.5	31.5	36.5	14	20.3	69.1	11.8	36.8	48.5	25	10.3	5	12.8	20
18	San Antonio Spurs	5	112.2	43.2	93.6	46.2	11.6	30.2	38.4	14.2	19.6	72.4	10.2	39.6	49.8	25.2	7.4	6.2	11.8	20.6
19	Orlando Magic	5	112.6	44.4	90.6	49	10.8	31.2	34.6	13	18.8	69.1	8.8	38	46.8	24.6	6.4	5.2	16.6	22.6
20	Detroit Pistons	5	115.2	40.6	85.6	47.4	13.2	35.4	37.3	20.8	28	74.3	7.6	38.2	45.8	25	8.8	6.8	16	22
21	Atlanta Hawks	5	115.4	41	92.8	44.2	12	39.4	30.5	21.4	25.4	84.3	10.6	32.6	43.2	22.6	6.8	4.4	11.2	22.8
22	Sacramento Kings	5	115.6	44.6	91.4	48.8	11.4	34.4	33.1	15	18.6	80.6	9	33.8	42.8	28	7.4	6	14.4	20
23	Portland Trail Blazers	5	115.8	41.4	88.4	46.8	13	37	35.1	20	24.8	80.6	8.4	38.2	46.6	24	6.2	5.6	13.2	21
24	Memphis Grizzlies	5	116.6	42.2	92.2	45.8	12.4	34.6	35.8	19.8	23.8	83.2	10.8	36.4	47.2	25.8	10.2	6.8	14.4	18.8
25	Denver Nuggets	5	117.4	41.6	86.4	48.1	14.8	35.2	42	19.4	24.2	80.2	10.4	30.4	40.8	28.2	6.4	4	14.6	24.8
26	Minnesota Timberwolves	5	118.6	45.4	90.8	50	12.6	33.6	37.5	15.2	20	76	9.2	38	47.2	28.6	6.2	6.6	15.2	19.8
27	Washington Wizards	6	120	42.7	89	47.9	11.7	32	36.5	23	29.2	78.9	9.5	35.5	45	23.8	8.3	3	14.7	22
28	Chicago Bulls	6	123.5	43	88.3	48.7	14.5	36.5	39.7	23	29.2	78.9	8.2	35.8	44	27	11.3	4.5	16.5	25.3
29	Houston Rockets	3	123.7	46	95	48.4	14.7	36.3	40.4	17	26.3	64.6	12	35.3	47.3	27	9.7	5.7	13.7	19.7
30	Golden State Warriors	5	124	42.2	90.4	46.7	17	38	44.7	22.6	29.6	76.4	11	42.4	53.4	24	8	5.2	17.2	22.6

 

[sandy666]

btw. in Excel 2019 Power Query is built-in

 

[srallen3]

is that what you want?

RK	Team	GP	PTS	FGM	FGA	FG%	3PM	3PA	3P%	FTM	FTA	FT%	OR	DR	REB	AST	STL	BLK	TO	PF
1	Phoenix Suns	6	98.7	37	82.2	45	8.5	28.3	30	16.2	22.8	70.8	8.2	35	43.2	20	6.3	3.3	14	20.3
2	New Orleans Pelicans	5	99.2	36.2	83.4	43.4	15	42.8	35	11.8	15.4	76.6	6.8	33.2	40	25.8	7.8	4.8	16.2	21.6
3	Philadelphia 76ers	5	99.8	36.8	88.8	41.4	10.8	30.4	35.5	15.4	19.4	79.4	9.8	34.8	44.6	23.2	8	5.2	15.8	22.8
4	Dallas Mavericks	5	103.6	37.4	81.6	45.8	11.2	33.8	33.1	17.6	23.4	75.2	9	36.6	45.6	23.4	6.8	4.2	16	24
5	Toronto Raptors	4	103.8	38.3	88	43.5	11	35.8	30.8	16.3	22.3	73	10	40	50	27	8.3	5.8	18.3	18.3
6	New York Knicks	5	105.2	38.8	89.6	43.3	10	37.4	26.7	17.6	24.6	71.5	12.4	35	47.4	23	8.4	7.2	13	20.6
7	Utah Jazz	5	106.2	39.2	89.6	43.8	14	33.2	42.2	13.8	17.6	78.4	8.4	31.8	40.2	22	7	5.4	10.4	19
8	Indiana Pacers	5	107	38	83.4	45.6	11.6	27	43	19.4	26.6	72.9	9.6	32	41.6	22.4	8.6	6.2	17.4	19.6
9	Los Angeles Lakers	6	107.8	40.3	90.5	44.6	12	35.2	34.1	15.2	20.5	74	8.8	31.8	40.7	23.5	9	5	12.3	21.5
10	Cleveland Cavaliers	5	108.2	38.8	86.6	44.8	14.8	37.2	39.8	15.8	22.8	69.3	9.8	35.4	45.2	25.6	10	4.2	19.4	19.8
10	Charlotte Hornets	5	108.2	39.6	86.8	45.6	13.4	38.8	34.5	15.6	22.4	69.6	9.6	37.8	47.4	28	9.2	5.4	16.4	18.8
12	LA Clippers	6	108.8	38	83.8	45.3	12.3	37.3	33	20.5	25.7	79.9	9.7	33.2	42.8	22.7	6	3	13.5	17.7
13	Milwaukee Bucks	6	110.5	40.5	88.8	45.6	12.7	35.7	35.5	16.8	21.7	77.7	7.8	33.8	41.7	21.7	7	5.5	15.8	19.8
14	Boston Celtics	6	111	41.8	86.5	48.4	11.3	31.7	35.8	16	20.8	76.8	9.8	33.8	43.7	22.3	7.7	5.5	16.7	19.3
15	Miami Heat	5	111.2	39.2	87.6	44.7	15.4	38.4	40.1	17.4	22.8	76.3	10.4	34.2	44.6	23.4	12	3.2	16.4	22.2
16	Brooklyn Nets	6	111.8	41.5	97.2	42.7	12	34.7	34.6	16.8	22.8	73.7	14.8	35.8	50.7	25.8	7.2	3.8	14.2	22
17	Oklahoma City Thunder	4	112	43.3	91.5	47.3	11.5	31.5	36.5	14	20.3	69.1	11.8	36.8	48.5	25	10.3	5	12.8	20
18	San Antonio Spurs	5	112.2	43.2	93.6	46.2	11.6	30.2	38.4	14.2	19.6	72.4	10.2	39.6	49.8	25.2	7.4	6.2	11.8	20.6
19	Orlando Magic	5	112.6	44.4	90.6	49	10.8	31.2	34.6	13	18.8	69.1	8.8	38	46.8	24.6	6.4	5.2	16.6	22.6
20	Detroit Pistons	5	115.2	40.6	85.6	47.4	13.2	35.4	37.3	20.8	28	74.3	7.6	38.2	45.8	25	8.8	6.8	16	22
21	Atlanta Hawks	5	115.4	41	92.8	44.2	12	39.4	30.5	21.4	25.4	84.3	10.6	32.6	43.2	22.6	6.8	4.4	11.2	22.8
22	Sacramento Kings	5	115.6	44.6	91.4	48.8	11.4	34.4	33.1	15	18.6	80.6	9	33.8	42.8	28	7.4	6	14.4	20
23	Portland Trail Blazers	5	115.8	41.4	88.4	46.8	13	37	35.1	20	24.8	80.6	8.4	38.2	46.6	24	6.2	5.6	13.2	21
24	Memphis Grizzlies	5	116.6	42.2	92.2	45.8	12.4	34.6	35.8	19.8	23.8	83.2	10.8	36.4	47.2	25.8	10.2	6.8	14.4	18.8
25	Denver Nuggets	5	117.4	41.6	86.4	48.1	14.8	35.2	42	19.4	24.2	80.2	10.4	30.4	40.8	28.2	6.4	4	14.6	24.8
26	Minnesota Timberwolves	5	118.6	45.4	90.8	50	12.6	33.6	37.5	15.2	20	76	9.2	38	47.2	28.6	6.2	6.6	15.2	19.8
27	Washington Wizards	6	120	42.7	89	47.9	11.7	32	36.5	23	29.2	78.9	9.5	35.5	45	23.8	8.3	3	14.7	22
28	Chicago Bulls	6	123.5	43	88.3	48.7	14.5	36.5	39.7	23	29.2	78.9	8.2	35.8	44	27	11.3	4.5	16.5	25.3
29	Houston Rockets	3	123.7	46	95	48.4	14.7	36.3	40.4	17	26.3	64.6	12	35.3	47.3	27	9.7	5.7	13.7	19.7
30	Golden State Warriors	5	124	42.2	90.4	46.7	17	38	44.7	22.6	29.6	76.4	11	42.4	53.4	24	8	5.2	17.2	22.6

I did not know that, yes that is what I want!

 

[sandy666]

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