Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |