I am trying to download data from the web directly into an Excel sheet using Power Query. The source of the data is:
I am using the Excel get data from the web tool
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
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"
