Import data from web with Column header abbreviation

[tmhalila]

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 observe it is an abbreviation text from the HTML code as attached below. Please assist in resolving this.

Here is the Link to the imported source: Link to Wikipedia

[jennratten]

Hello - can you please post your Power Query script?  You can do this by opening the Advanced Editor, copying everything and pasting it here.  Thanks!

[tmhalila]

Thanks for following request here is the query

[jennratten]

Can you please paste it as text so that we can test with it?

[tmhalila]

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.jquery-tablesorter:nth-child(12) > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(6), TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE.wikitable.sortable.jquery-tablesorter: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.jquery-tablesorter: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.jquery-tablesorter:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE.wikitable.sortable.jquery-tablesorter: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.jquery-tablesorter: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.jquery-tablesorter:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column4", "TABLE.wikitable.sortable.jquery-tablesorter: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.jquery-tablesorter: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.jquery-tablesorter:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column5", "TABLE.wikitable.sortable.jquery-tablesorter: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.jquery-tablesorter: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.jquery-tablesorter:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column6", "TABLE.wikitable.sortable.jquery-tablesorter: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.jquery-tablesorter: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.jquery-tablesorter:nth-child(12) > * > TR > TH[colspan=""6""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE.wikitable.sortable.jquery-tablesorter:nth-child(12) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Bottom Rows",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Video name", type text}, {"Uploader / artist", type text}, {"Likes (millions)", type number}, {"Upload date", type date}, {"Notes", type text}})
in
    #"Changed Type1"

[jennratten]

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]

Once you try to refresh, it gives an error that Column No. is not found