Power BI - 加载保存为 .xls 的 *.htm 文件 [英] Power BI - load *.htm file saved as .xls

问题描述

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("\serverAShareB$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),
    #"\serverAShareB$Desktop\_Raw Data Dump 2018-Oct-4 6-00-53 AM xls" = #"Filtered Rows1"{[#"Folder Path"="\serverAShareB$Desktop",Name="Raw Data Dump 2018-Oct-4 6-00-53 AM.xls"]}[Content]
in
    #"\ServerAShareB$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.

解决方案

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:UsersaolsonDownloadsexample-html.xls"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls")),
    #"C:UsersaolsonDownloadsexample-html xls\_example-html xls" = #"Filtered Rows"{[#"Folder Path"="C:UsersaolsonDownloadsexample-html.xls",Name="example-html.xls"]}[Content],
    #"Imported Excel" = Web.Page(#"C:UsersaolsonDownloadsexample-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].

这篇关于Power BI - 加载保存为 .xls 的 *.htm 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！