Dec 01 2021 08:26 PM
Hello, I'm trying to get data from a table of URLs (for different stocks). Please see attached file.
I've created a connection using one URL and then tried to get data from the table of URLs. I'm encountering the below error while the query is downloading:
Expression.Error: There weren't enough elements in the enumeration to complete the operation
Below are the codes in the Advanced Editor:
let
Source = Excel.CurrentWorkbook(){[Name="URL_Annual"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticker", Int64.Type}, {"URL_Annual", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fxOther_Annual([URL_Annual])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7"})
in
#"Expanded Custom"
Would someone be able to help with this error please?
Thanks very much!
Dec 01 2021 10:45 PM
Solution@nes_k4 Your query and function work just fine for all tickers where the data you want to extract is in the 8th table in the particular URL. However, some ticket numbers don't always return 8 tables (0 to 7). Inserted a step directly after the Source that filters for the table called "cnhk-list2", whatever list of tables is returned. Then it seems to work as desired. See attached.
Dec 02 2021 01:29 AM - edited Dec 02 2021 01:42 AM
Thank you so much for your help! It works perfectly!
Dec 01 2021 10:45 PM
Solution@nes_k4 Your query and function work just fine for all tickers where the data you want to extract is in the 8th table in the particular URL. However, some ticket numbers don't always return 8 tables (0 to 7). Inserted a step directly after the Source that filters for the table called "cnhk-list2", whatever list of tables is returned. Then it seems to work as desired. See attached.