Forum Discussion

poweruser27's avatar
poweruser27
Frequent Visitor
3 years ago

Extract Table from Webpage

Hello, I'm trying extract the table from below URL...but I only get the column names of the table without actual data AQI Current Hour Summary (wa.gov)  
Data Protection
Need Help
Ahmedx's avatar
Ahmedx
Super User
3 years ago

pls try URL adress

https://enviwa.ecology.wa.gov/Report/LatestAQI
or

let
    Source = Web.BrowserContents("https://enviwa.ecology.wa.gov/Report/LatestAQI"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.k-selectable > * > TR > TH[rowspan=""1""]:not([colspan]):nth-child(1):nth-last-child(6), TABLE.k-selectable > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6)"}, {"Column2", "TABLE.k-selectable > * > TR > TH[rowspan=""1""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""1""]:not([colspan]):nth-child(2):nth-last-child(5), TABLE.k-selectable > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5)"}, {"Column3", "TABLE.k-selectable > * > TR > TH[rowspan=""1""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""1""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[rowspan=""1""]:not([colspan]):nth-child(3):nth-last-child(4), TABLE.k-selectable > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4)"}, {"Column4", "TABLE.k-selectable > * > TR > TH[rowspan=""1""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""1""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[rowspan=""1""]:not([colspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""1""]:not([colspan]):nth-child(4):nth-last-child(3), TABLE.k-selectable > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3)"}, {"Column5", "TABLE.k-selectable > * > TR > TH[rowspan=""1""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""1""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[rowspan=""1""]:not([colspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""1""]:not([colspan]):nth-child(4):nth-last-child(3) + TH[rowspan=""1""]:not([colspan]):nth-child(5):nth-last-child(2), TABLE.k-selectable > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(2)"}, {"Column6", "TABLE.k-selectable > * > TR > TH[rowspan=""1""]:not([colspan]):nth-child(1):nth-last-child(6) + TH[rowspan=""1""]:not([colspan]):nth-child(2):nth-last-child(5) + TH[rowspan=""1""]:not([colspan]):nth-child(3):nth-last-child(4) + TH[rowspan=""1""]:not([colspan]):nth-child(4):nth-last-child(3) + TH[rowspan=""1""]:not([colspan]):nth-child(5):nth-last-child(2) + TH[rowspan=""1""]:not([colspan]):nth-child(6):nth-last-child(1), TABLE.k-selectable > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(1)"}}, [RowSelector="TABLE.k-selectable > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"County", type text}, {"Site", type text}, {"AQI Level", type text}, {"Pollutant", type text}, {"Occured", type datetime}, {"AQI", Int64.Type}})
in
    #"Changed Type"