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

问题描述

我有一个要求，我必须导入保存为。* htm，。* html

的 .xls 文件

打开文件并单击另存为，通常会显示文件所在的格式，这就是它的外观-

我点击了Binary它向我显示了.xls文件，并以 Excel工作簿，csv，文本，html，xml 的形式打开了该文件，但它没有用：-

以Excel Workbook打开它会给我这个错误-外部表未处于预期格式。

我们如何在内部加载此错误Power BI。数据存在于Sheet1中，从第5行开始。我一直在尝试通过加载它来解决这个问题。

我无法编辑和重新保存文件手动将其作为.xlsx，因为它无法自动执行。
还要注意，将其另存为.xlsx文件，它可以正常工作。但这不是我所需要的。

请帮我解决这个问题。

我确实提到了这篇文章-但我不熟悉M查询

我的完整查询如下：

  let 
 Source =文件夹。 Files（ C：\Users\aolson\Downloads\example-html.xls），
＃ Filtered Rows = Table.SelectRows（Source，每个（[Extension] = .xls ）），
＃ C：\Users\aolson\Downloads\example-html xls\_example-html xls =＃ Filtered Rows {[## Folder Path = C： \用户\aolson\下载\example-html.xls\，Name = example-html.xls]} [内容]，
＃导入的Excel = Web .Page（＃ C：\Users\aolson\Downloads\example-html xls\_example-html xls）{0} [数据] 
 in 
＃导入的Excel 
  

注意：单击上面指示的表格等效于选择<$ c $中的单元格值c> Data 列和第0行，因此 {0} [Data] 。

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.

解决方案

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].

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