PowerBI having issues getting data from htm file on sharepoint with 4 tables in.

[gjelstrup]

PowerBI having issues getting data from htm file on sharepoint with 4 tables in.

It works fine when the htm file is stored in local folder, where powerBI can read the 4 tables in the htm file. The htm file on sharepoint looks fine with 4 tables in. We can find the file name as binary data from "get data from sharepoint folder", but powerBI cannot read table 1-4 is sees the file as empty.

Can anybody help me?

[PhilipTreacy · Accepted solution]

Hi @gjelstrup 

To extract the tables from a HTML file PQ uses Html.Table

Html.Table - PowerQuery M | Microsoft Docs

this requires that you tell it how to find the table in the HTML.  You'd normally do this via the GUI as you import the web page using the web connector.

But when you use the Sharepoint Folder connector you don't get toi do this.

To get around this you can open your original query that loads the file from your local disk, and copy out the Html.Table step and paste that into your new query that loads from Sharepoint.

Be sure to change the first parameter in the Html.Table call to match the name of the previous step in your new query which holdfs your HTML file.

You'll need to do this for every table in your HTML file.

See below for example code, I didnt' want to clutter up my reply with this as there's quite a bit.

Regards

Phil

Loading HTML From Local File

let
    Source = File.Contents("file:///D:/temp/table.htm"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(6), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column7", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(5), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column8", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(4), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column9", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(3), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(11):nth-last-child(1), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column12", "TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE[id='t1'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"_1", type text}, {"Team", type text}, {"P", Int64.Type}, {"W", Int64.Type}, {"D", Int64.Type}, {"L", Int64.Type}, {"F", Int64.Type}, {"A", Int64.Type}, {"GD", Int64.Type}, {"Pts", Int64.Type}, {"Column12", type text}})
in
    #"Changed Type"

Loading the Same File From Sharepoint

let
    Source = SharePoint.Files("https://365moth.sharepoint.com/sites/PGTWork/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".htm")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    Content = #"Removed Columns"{0}[Content],
    #"Extracted Table From Html" = Html.Table(Content, {{"Column1", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column3", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(9), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(9), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column4", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column7", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(4), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(4), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column9", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(3), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column10", "TABLE[id='t1'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(10):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(11):nth-last-child(1), TABLE[id='t1'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(11) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(10):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(11):nth-last-child(1), TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column12", "TABLE[id='t1'] > * > TR > TD[colspan=""12""]:not([rowspan]):nth-child(1):nth-last-child(1)"}}, [RowSelector="TABLE[id='t1'] > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", Int64.Type}, {"_1", type text}, {"Team", type text}, {"P", Int64.Type}, {"W", Int64.Type}, {"D", Int64.Type}, {"L", Int64.Type}, {"F", Int64.Type}, {"A", Int64.Type}, {"GD", Int64.Type}, {"Pts", Int64.Type}, {"Column12", type text}})
in
    #"Changed Type"