Importing content from HTM files from Sharepoint folder

[danieltodorov]

Hi Guys,

I'm trying to set up a data load, in Power BI desktop (and later in the service, as a dataflow), which merges daily extracts, that are stored in a Sharepoint folder, in HTM format.

Each file has a table of the same structure embedded, and Power BI is able to recognize it as Table0. I can load it as data table and transform it, and using Local folder option (from my laptop), I can combine the Table0 from every file no problem.

However, when I try the exact same process, but with a sharepoint folder as a source, Power BI cannot read the content of the HTM files, instead, I get a Navigation to the webpage was canceled message:

and at the same time, I get a popup window, this $value file being offered for download:

The $value file actually contans the htm file content, which PBI would need to analyze.

Can you help me, what to set or check, to make it actually read the htm content, instead of downloading it?

ps.: I have read permission on Sharepoint for the base URL and the files themselves too.

[deepikaajith]

what is the solution to this problem. I am also facing the same issue

[Stachu]

can you share the M code that PowerBi generated for this query?

[danieltodorov]

Coming back to the issue today, I got  different error message. Now I cant even select the table from the htm file to be mereged, because I get this message on the Comibne Files screen:

I started it from blank pbix, so I have no other queries my parameter can interefere with. Since I cannot hit OK, I doesnt generate the m query which would be worth sharing. 

So I Cancelled and extracted the Content columns's Binary data from one file, there I get to the same error I originally posted. The M query looks like this:

let
Source = SharePoint.Files("https://company.sharepoint.com/sites/Move/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "VBFA_")),
#"2020-04-24T08_55_56+00_00 VBFA_ Display of Entries Found htm_https://company sharepoint com/sites/Move/Shared Documents/DATA/Blank subject emails (mostly SAP data)/" = #"Filtered Rows"{[Name="2020-04-24T08_55_56+00_00 VBFA_ Display of Entries Found.htm",#"Folder Path"="https://company.sharepoint.com/sites/Move/Shared Documents/DATA/Blank subject emails (mostly SAP data)/"]}[Content],
#"Imported HTML" = Web.Page(#"2020-04-24T08_55_56+00_00 VBFA_ Display of Entries Found htm_https://company sharepoint com/sites/Move/Shared Documents/DATA/Blank subject emails (mostly SAP data)/"),
Data = #"Imported HTML"{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data,{{"Column1", type text}, {"Column2", type text}})
in
#"Changed Type"

So this query gives me the original "Navigation to the webpage was canceled" error. I did one modification before posting, changed the company's actual name to company.

[Vera_33]

Hi @danieltodorov @deepikaajith ,

I came across the same issue, so when you locate your file in SharePoint, add a custom column to read the Text first, before Web.Page, if you have mutiple tables constructed in the HTM file, you may need to add a custom column to count the columns so you can find those tables you want. Let me know if you need any further help.

Source = SharePoint.Files("https://company.sharepoint.com/sites/Move/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "VBFA_")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.FromBinary([Content])),
Custom1 = Web.Page( #"Added Custom"[Custom]{0})
in
Custom1

[deepikaajith]

Hi,

Can you show us with some example so we can follow the sma esteps in our file?

[Vera_33]

Hi @deepikaajith 

How about you posting your M code and screenshot of your Power Query Editor (hide anything sensitive)? I can help from there.

[lucamanunta]

The solution proposed by Vera_33 worked as a charm for me! Thank you very much!

Just to clarify, I did not even need to customise the code she proposed, so I just copy/pasted the following in the editor:

#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.FromBinary([Content])),
    Custom1 = Web.Page( #"Added Custom"[Custom]{0}),
    Data = Custom1{0}[Data],