Forum Discussion
tmhalila
3 years agoResolver II
Import data from web with Column header abbreviation
Hello there, I have imported a web page successfully in Power BI, but when I refresh the data, it fails with the error message column not found. I have tried to inspect the Column and come to observ...
jennratten
3 years agoSuper User
This script works for me.
let
Source = Web.BrowserContents("https://en.wikipedia.org/wiki/List_of_most-liked_YouTube_videos"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.wikitable.sortable:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6), TABLE.wikitable.sortable:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6), TABLE.wikitable.sortable:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE.wikitable.sortable:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5), TABLE.wikitable.sortable:nth-child(12) > * > 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), TABLE.wikitable.sortable:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE.wikitable.sortable:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4), TABLE.wikitable.sortable:nth-child(12) > * > 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), TABLE.wikitable.sortable:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column4", "TABLE.wikitable.sortable:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3), TABLE.wikitable.sortable:nth-child(12) > * > 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), TABLE.wikitable.sortable:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column5", "TABLE.wikitable.sortable:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(2), TABLE.wikitable.sortable:nth-child(12) > * > 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), TABLE.wikitable.sortable:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column6", "TABLE.wikitable.sortable:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(1), TABLE.wikitable.sortable:nth-child(12) > * > 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), TABLE.wikitable.sortable:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE.wikitable.sortable:nth-child(12) > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"No.", type text}, {"Video name", type text}, {"Uploader / artist", type text}, {"Likes (millions)", type text}, {"Upload date", type text}, {"Notes", type text}})
in
#"Changed Type1"
tmhalila
3 years agoResolver II
Once you try to refresh, it gives an error that Column No. is not found