Create query from altered website

[DRSteele]

Can someone please help me create a query to get the table from this website? I had it working but the website has changed itself to a new style.

Website: NHL.com - Stats

 

[SimonNU]

It's not easy - my suggestion is to load up Chromes element inspection and use that in conjunction with Power Query. I was able to get close to the data you need using this method and the below script:

let
Source = Web.Page(Web.Contents("http://www.nhl.com/stats/team?reportType=season&report=teamsummary&season=20152016&gameType=2&sort=teamFullName&aggregate=0")),
Data = Source{1}[Data],
Children = Data{0}[Children],
Children1 = Children{1}[Children],
Children2 = Children1{15}[Children],
Children3 = Children2{2}[Children],
Children4 = Children3{2}[Children],
Children5 = Children4{4}[Children],
Children6 = Children5{0}[Children],
Children7 = Children6{1}[Children],
Children8 = Children7{0}[Children],
Children9 = Children8{0}[Children],
#"Expanded Children" = Table.ExpandTableColumn(Children9, "Children", {"Kind", "Name", "Children", "Text"}, {"Children.Kind", "Children.Name", "Children.Children", "Children.Text"}),
#"Expanded Children.Children" = Table.ExpandTableColumn(#"Expanded Children", "Children.Children", {"Kind", "Name", "Children", "Text"}, {"Children.Children.Kind", "Children.Children.Name", "Children.Children.Children", "Children.Children.Text"})
in
#"Expanded Children.Children"


Good luck!

 

[DRSteele]

Thank you for your efforts. It's all hieroglyphics to me...man! how does someone acquire this type of knowledge?

I tried your script in the Advanced Editor and it tripped on the Navigation step, something about an error on 'Children8.' I suspect that the website changed itself again and that every time we get it sorted out the website will change yet again. What a pain; I guess they don't want people to scrape their webpages. Have you any insights?

 

[SimonNU]

Power Query will pick up properly formatted <table> tags but as you can see there are often times that websites contain tables in not-so-properly-formatted <table> tags. I'm not sure why it doesn't work for you because it's still loading up the table data for me (even though it's horribly formatted, that part I leave to you). Perhaps I'm seeing a Canadian version of the website?

You can try troubleshooting the problem yourself:
Navigate to the table in Chrome. Right click -> "Inspect".
The HTML on the right should point to "#stats-data-table". This is your target in Power Query. Keep this open in the background.
Connect to the Document in Power Query, expand the HTML table you see, then expand the BODY table.
Now the fun part, this giant list of non-descriptive/useless IFRAME, SCRIPT, NOSCRIPT, DIV etc... records are in the exact same order/hierarchy as the informative Chrome element inspector. It's just a matter of using Chrome to help you navigate through Power Query's mess.