Excel Power Query "External table is not in the expected format."-CodePudding

I am trying to download data from the web directly into an Excel sheet using Power Query. The source of the data is:

When I run this I get the following error message:

I can download the file if I paste the link into a Chrome browser.

Excel version I am using: Microsoft® Excel® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20278) 64-bit

Any help would be appreciated as have hit a dead end.

Thank you

CodePudding user response：

The excel workbook has been incorrectly saved. You can work around this by loading as text and cleaning up the 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"