OK. Let's begin. We'll have to do this in small steps that work with my layout, otherwise I'll become all confused and my head will ache.
Create a sheet called Q_Data. Create a blank query called Q_Scores_18_HR and paste this MCode into the blank query. 'Close and Load to' and put it in Q_Data in cell A40. Your table should end up in cells A40 to H1311.
Then we will name this as an ExcelTable T_Scr and begin to add many columns to that ExcelTable to create more essential data.
let
Source = Web.Page(Web.Contents("http://www.hockey-reference.com/leagues/NHL_2018_games.html")),
Data1 = Source{1}[Data],
#"Changed Type2" = Table.TransformColumnTypes(Data1,{{"Date", type date}, {"Visitor", type text}, {"G", type text}, {"Home", type text}, {"G2", type text}, {"", type text}, {"Att.", type text}, {"LOG", type text}, {"Notes", type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"Date", type date}, {"Visitor", type text}, {"G", type text}, {"Home", type text}, {"G2", type text}, {"", type text}, {"Att.", type text}, {"LOG", type text}, {"Notes", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Att.", "LOG", "Notes"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Visitor", "Home", "G", "G2", ""}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"G", "VScr"}, {"G2", "HScr"}, {"", "type"}, {"Home", "Host"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [VScr] = null then null else if [type] = "SO" then "S/O" else if [type] <> null then [type] else "R" ),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Result"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"type"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Scoreline", each [Visitor] &" " & [VScr] &" - " & [Host] &" " & [HScr] &" "&[Result]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Date", "Visitor", "Host", "Scoreline", "VScr", "HScr", "Result"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"VScr", Int64.Type}, {"HScr", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Visitor", "Host", "Scoreline", "VScr", "HScr", "Result"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"Index", "Gm"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Result", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type3", each true)
in
#"Filtered Rows"
