Forum Discussion

tmhalila's avatar
tmhalila
Resolver II
3 years ago

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...
Need Help
tmhalila's avatar
tmhalila
Resolver II
3 years ago
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's avatar
jennratten
Super User
3 years ago

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's avatar
    tmhalila
    Resolver II
    3 years ago

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