Using Power Query to source from web (html to table?)

%3CLINGO-SUB%20id%3D%22lingo-sub-2389643%22%20slang%3D%22en-US%22%3EUsing%20Power%20Query%20to%20source%20from%20web%20(html%20to%20table%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389643%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20in%20Melbourne%20Australia%2C%20and%20have%20been%20tasked%20with%20filtering%20our%20Covid%20Contact%20sites%20to%20focus%20on%20the%20regions%20where%20the%20org%20I%20work%20for%20operates....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.coronavirus.vic.gov.au%2Fexposure-sites%23case-alerts--public-exposure-sites%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.coronavirus.vic.gov.au%2Fexposure-sites%23case-alerts--public-exposure-sites%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%202%20solutions%20to%20the%20problem%2C%20depending%20on%20how%20the%20site%20was%20set%20up%2C%20but%20now%20they've%20changed%20set%20up%20again%20and%20I%20don't%20think%20I%20can%20extract%20a%20table%20easily.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPreviously%2C%20they%20just%20had%20the%20sites%20listed%20in%20a%20table%2C%20so%20power%20query%20did%20the%20job%20easily.%20get%20data%20from%20web%2C%20clean%20it%20up%20as%20I%20wanted%20it%2C%20add%20a%20vlookup%2C%20job%20done.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20also%20worked%20out%20a%20more%20convoluted%20way%20where%20I%20literally%20copy%20and%20pasted%20what%20was%20on%20the%20web%20page%20and%20then%20used%20formulas%20to%20get%20the%20data%2C%20but%20now%20the%20site%20has%20set%20it%20up%20differently%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20easily%20extract%20the%20contact%20site%20list%20if%20there%20isn't%20an%20obvious%20table%20for%20Power%20Query%20to%20pick%20up%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2389643%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391342%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Power%20Query%20to%20source%20from%20web%20(html%20to%20table%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfraid%20not%2C%20now%20these%20are%20dynamic%20tables%20generated%20by%20java%20script%2C%20PQ%20doesn't%20work%20with%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I am in Melbourne Australia, and have been tasked with filtering our Covid Contact sites to focus on the regions where the org I work for operates....

 

https://www.coronavirus.vic.gov.au/exposure-sites#case-alerts--public-exposure-sites

 

I had 2 solutions to the problem, depending on how the site was set up, but now they've changed set up again and I don't think I can extract a table easily.

 

Previously, they just had the sites listed in a table, so power query did the job easily. get data from web, clean it up as I wanted it, add a vlookup, job done. 

 

I'd also worked out a more convoluted way where I literally copy and pasted what was on the web page and then used formulas to get the data, but now the site has set it up differently again.

 

Is there a way to easily extract the contact site list if there isn't an obvious table for Power Query to pick up?

 

2 Replies

@Davidm54 

Afraid not, now these are dynamic tables generated by java script, PQ doesn't work with them.

Thanks Sergei, Yeah, discovered that the hard way. I did discover, after asking a web designer friend, that there are chrome extensions that can scrape them to set up tables.

https://chrome.google.com/webstore/detail/table-capture/iebpjdmgckacbodjpijphcplhebcmeop?utm_source=...

There' still a bit of manual handling to do before you get there, but It's better than what I was doing originally, and I reckon I can make it a quicker process with some more tinkering.