Excel Power查询“外部表格”不采用预期格式。”

Question

我正在尝试使用电源查询将数据直接下载到Excel表中。数据的来源是：

grnv Fund Holdings数据

我正在使用Excel从Web工具获取数据

​

​nofollow noreferrer“>

如果我将链接粘贴到chrome浏览器中，我可以下载文件。

我正在使用的Excel版本： Microsoft®Excel®用于Microsoft 365 MSO（版本2205 Build 16.0.15225.20278）64位，

任何帮助都将被击中死胡同。

谢谢

Answer 1

Excel工作簿已被错误保存。您可以通过加载文本并清理HTML来解决此问题。

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.vaneck.com.au/FundHoldings.aspx?ticker=GRNV"), null, null, 1252)}),
    #"Filtered Rows" = Table.SelectRows(Source, each [Column1] <> "<tr>"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Column1] <> "<table cellpadding=""0"" cellspacing=""0"" border=""1"">"),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each [Column1] <> "</table>"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows2", "Column1", Splitter.SplitTextByDelimiter("</td><td>", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","<tr>","",Replacer.ReplaceText,{"Column1.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","<td>","",Replacer.ReplaceText,{"Column1.1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","</td></tr>","",Replacer.ReplaceText,{"Column1.6"}),
    #"Filtered Rows3" = Table.SelectRows(#"Replaced Value2", each [Column1.1] <> "<td colspan=6>All Fund Holdings as at 23/06/2022</td></tr>"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows3", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Number", Int64.Type}, {"Security name", type text}, {"ASX Code", type text}, {"Shares", Int64.Type}, {"Market Value", type text}, {"% of Fund net assets", Percentage.Type}})
in
    #"Changed Type1"