Selecting data from multiple tables from a single web source using M

[xzfujc]

I want to get the data from this NBA data from this web page: http://www.espn.com/nba/standings/_/season/2018

By using that URL in the get data section, I get back 4 tables. A table of the team names from the eastern conference, a table of thier regular season stats,  a table of the team names from the western conference, and a table of thier regular season stats.

In the advance editor of the query editor for table 0 I have the following:

let
Source = Web.Page(Web.Contents("http://www.espn.com/nba/standings/_/season/2018")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}})
in
#"Changed Type"

In the advance editor of the query editor for table 1 I have the following:

let
Source = Web.Page(Web.Contents("http://www.espn.com/nba/standings/_/season/2018")),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"W", Int64.Type}, {"L", Int64.Type}, {"PCT", type number}, {"GB", type text}, {"HOME", type text}, {"AWAY", type text}, {"DIV", type date}, {"CONF", type text}, {"PPG", type number}, {"OPP PPG", type number}, {"DIFF", type number}, {"STRK", type text}, {"L10", type text}})
in
#"Changed Type"

I want all of the data from each source in one table, but for now how can I use M to get the data from tables 0 and table 1 into a single table? 

I have tried the following:

let
Source = Web.Page(Web.Contents("http://www.espn.com/nba/standings/_/season/2018")),
Data0 = {Source{0}[Data], Source{1}[Data]},
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text},{"W", Int64.Type}, {"L", Int64.Type}, {"PCT", type number}, {"GB", type text}, {"HOME", type text}, {"AWAY", type text}, {"DIV", type date}, {"CONF", type text}, {"PPG", type number}, {"OPP PPG", type number}, {"DIFF", type number}, {"STRK", type text}, {"L10", type text}})
in
#"Changed Type"

But I get the error message: "Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=List
Type=Type"

Any suggestions?

[MFelix · Accepted solution]

Hi @xzfujc,

Combining both tables as you are doing is making the same as an append you need to merge both tables and then you will have a single result.

Try the coding below:

let
    Source = Web.Page(Web.Contents("http://www.espn.com/nba/standings/_/season/2018")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    Data1 = Source{1}[Data],
    #"Added Index1" = Table.AddIndexColumn(Data1, "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"W", "L", "PCT", "GB", "HOME", "AWAY", "DIV", "CONF", "PPG", "OPP PPG", "DIFF", "STRK", "L10"}, {"W", "L", "PCT", "GB", "HOME", "AWAY", "DIV", "CONF", "PPG", "OPP PPG", "DIFF", "STRK", "L10"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"", type text}, {"W", Int64.Type}, {"L", Int64.Type}, {"PCT", type number}, {"GB", type text}, {"HOME", type text}, {"AWAY", type text}, {"DIV", type text}, {"CONF", type text}, {"PPG", type number}, {"OPP PPG", type number}, {"DIFF", type number}, {"STRK", type text}, {"L10", type text}})
in
    #"Changed Type1"

What I'm doing here is to get information from table0 and adding an index column, then picking up table 1 and also adding an index, finally merging the two tables by the index column.

Regards,

MFelix