Power BI - load *.htm file saved as .xls

Question

I have a requirement where I have to import an .xls file which is saved as .*htm, .*html

Opening the file and clicking on saving as, will usually show the format that the file is in and this is how it looks -

I clicked on Binary and it showed me the .xls file and I opened it as Excel workbook, csv, text, html, xml but it didn't work :-

Opening it as Excel Workbook gives me this error - Eternal Table is not in the Expected Format.

How do we load this inside Power BI. The data is present in Sheet1 starting from Row Number 5. I have been struggling with this by trying to load it.

I can't edit and re-save the file manually as .xlsx, as it cannot be automated. Also to note, saved it as a .xlsx file and it works fine. But that's not what I need.

Kindly help me with this.

I did refer to this article - but I am not familiar with M query

https://social.technet.microsoft.com/Forums/en-US/c58f8b19-b2d6-4ef6-890b-f10c62c0096f/external-table-is-not-in-the-expected-format?forum=powerquery

Other Similar Question - https://www.experts-exchange.com/questions/29064908/HTML-Excel-file-Power-Query-BI-Connection-can-this-be-done.html

This is how My M-Code looks like,

let
    Source = Folder.Files("\\serverA\ShareB$\Desktop"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
    #"\\serverA\ShareB$\Desktop\_Raw Data Dump 2018-Oct-4 6-00-53 AM xls" = #"Filtered Rows1"{[#"Folder Path"="\\serverA\ShareB$\Desktop\",Name="Raw Data Dump 2018-Oct-4 6-00-53 AM.xls"]}[Content]
in
    #"\\ServerA\ShareB$\Desktop\_Raw Data Dump 2018-Oct-4 6-00-53 AM xls"

Adding a sample file here for reference on the file type -

https://www.glump.net/_media/howto/web/serve-html-as-an-excel-file-from-a-web-application/example-html.xls.zip

I tried with the above file on the link and it produces the same error. So, the question here is how to load the file in the above link inside Power BI, without changing the file manually.

What does your M code look like when you load this? (You can find the code under Advanced Editor). — Alexis Olson, Oct 04 '18 at 13:44
I'm not sure I understand what you mean by and .xls saved as an .html. Is there an example file you can share? — Alexis Olson, Oct 04 '18 at 15:12
@AlexisOlson It's a confidential file and can't really share it. But, the one thing that I believe it has .html in it is, when I open the file manually and click on save as - "it gives me the attached screenshot", over there you could see Webpage type. This as much as I know, will always show the actual format of the file.If I change that to .xlsx and save it and open it, then it works again. — Student of the Digital World, Oct 04 '18 at 15:23
Can you strip the file of any confidential or superfluous information and share that? I don't think I can help unless I can replicate the error and I haven't managed to do that playing with my own xls files. — Alexis Olson, Oct 04 '18 at 15:55
@AlexisOlson I understand. I went on to find a similar file online - This is my file type - https://www.glump.net/_media/howto/web/serve-html-as-an-excel-file-from-a-web-application/example-html.xls.zip — Student of the Digital World, Oct 04 '18 at 16:17
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/181299/discussion-between-sid29-and-alexis-olson). — Student of the Digital World, Oct 04 '18 at 16:29
@AlexisOlson The link to the extended question - https://stackoverflow.com/questions/52664525/power-bi-load-htm-file-saved-as-xls-starting-from-row-number-5 Please help me solving this. Thanks for your efforts. — Student of the Digital World, Oct 05 '18 at 11:22

score 2 · Accepted Answer · answered Oct 04 '18 at 16:58

2

Using the sample file you linked, I was able to load the file using Web.Page rather than Excel.Workboook and then clicking on the table shown below:

My full query looks like this:

let
    Source = Folder.Files("C:\Users\aolson\Downloads\example-html.xls"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),
    #"C:\Users\aolson\Downloads\example-html xls\_example-html xls" = #"Filtered Rows"{[#"Folder Path"="C:\Users\aolson\Downloads\example-html.xls\",Name="example-html.xls"]}[Content],
    #"Imported Excel" = Web.Page(#"C:\Users\aolson\Downloads\example-html xls\_example-html xls"){0}[Data]
in
    #"Imported Excel"

Note: Clicking on the table indicated above is equivalent to selecting the cell value in the Data column and row 0, hence {0}[Data].

answered Oct 04 '18 at 16:58

Alexis Olson

38,724
7
42
64

1

This works only if the first row starts from line number 1 and it doesn't work if it starts from line number 5 or 6...Please help me with that one. I am accepting this answer because my question wasn't very clear and you have answered the requirements. I will open that as a new question. – Student of the Digital World Oct 05 '18 at 11:14
You just need to filter the table until you have only have the single row you want or else use a different row reference instead of `{0}`, but yes, feel free to post that question separately if you need further clarification on that part. – Alexis Olson Oct 05 '18 at 13:28
Yes posted it here https://stackoverflow.com/questions/52664525/power-bi-load-htm-file-saved-as-xls-starting-from-row-number-5 – Student of the Digital World Oct 05 '18 at 13:29
That's the thing, it is not showing the data required for me to filter it. The reference isn't available. Just source = Service is present and source = table is not present at all. – Student of the Digital World Oct 05 '18 at 13:30
I see what you mean now. If I figure it out, I'll answer the other question. – Alexis Olson Oct 05 '18 at 13:33
Thanks. Much appreciate it. – Student of the Digital World Oct 05 '18 at 13:34

Power BI - load *.htm file saved as .xls

1 Answers1

Linked