Forum Discussion
Get excel file from URL as custom function
Hi all,
I have a table of excel file URLs as a result of a complex query to extract the underlying link addesses from the page
After some effort, I have a table of Release Date , Region , and FileURL. (You will not get the URL directly !!)
The file URL is visible when hovering over region hyperlink, and I have confirmed that each of the FileURL's are correctly downloaded and queried singly with a step of the form Source = Excel.Workbook(Web.Contents("https://www.aemo.com.au/..../_May_2019.xlsx"), null, true)
However when trying to create a custom function and parameterise the FileURL , the invoked custom function returns "an error occurred in the ‘ExistGen’ query. DataSource.Error: The remote name could not be resolved: 'fileurl'
I have removed the privacy setting as suggested by other similar posts.
I noted also that individual files are temporarily created locally when trying the query on a single file - so
************
Is a custom function calling a sheet of an excel file which is yet to be retrieved from a URL (passed as a parameter) even possible ??
************
It seems the simplest workaround would be to download all files to a local folder first, but that would defeat the purpose of having this updated dynamically as new files and links are updated infrequently from the web page.
Any clues appreciated
If my understanding is correct, this shouldn't be a problem, so there must be an error in your function or how you use it.
Please check the following code that includes a mockup:
let fn = (FileURL) => let Source = Excel.Workbook(Web.Contents(FileURL), null, true), existingnstable_Table = Source{[Item="existingnstable",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(existingnstable_Table,{{"Power Station", type text}, {"Owner", type text}, {"Nameplate Capacity (MW)", type number}, {"Technology Type", type text}, {"Fuel Type", type text}, {"Service Status", type text}, {"Region", type text}, {"summary_bucket", type text}, {"summary_status", type text}}) in #"Changed Type", Source = "https://www.aemo.com.au/-/media/Files/Electricity/NEM/Planning_and_Forecasting/Generation_Information/May-2019/Generation_Information_QLD_May_2019.xlsx", #"Converted to Table" = #table(1, {{Source}}), #"Appended Query" = Table.Combine({#"Converted to Table", #"Converted to Table"}), #"Invoked Custom Function" = Table.AddColumn(#"Appended Query", "fn", each fn([Column1])), #"Expanded fn" = Table.ExpandTableColumn(#"Invoked Custom Function", "fn", {"Power Station", "Owner", "Nameplate Capacity (MW)", "Technology Type", "Fuel Type", "Service Status", "Region", "summary_bucket", "summary_status"}, {"Power Station", "Owner", "Nameplate Capacity (MW)", "Technology Type", "Fuel Type", "Service Status", "Region", "summary_bucket", "summary_status"}) in #"Expanded fn"
2 Replies
If my understanding is correct, this shouldn't be a problem, so there must be an error in your function or how you use it.
Please check the following code that includes a mockup:
let fn = (FileURL) => let Source = Excel.Workbook(Web.Contents(FileURL), null, true), existingnstable_Table = Source{[Item="existingnstable",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(existingnstable_Table,{{"Power Station", type text}, {"Owner", type text}, {"Nameplate Capacity (MW)", type number}, {"Technology Type", type text}, {"Fuel Type", type text}, {"Service Status", type text}, {"Region", type text}, {"summary_bucket", type text}, {"summary_status", type text}}) in #"Changed Type", Source = "https://www.aemo.com.au/-/media/Files/Electricity/NEM/Planning_and_Forecasting/Generation_Information/May-2019/Generation_Information_QLD_May_2019.xlsx", #"Converted to Table" = #table(1, {{Source}}), #"Appended Query" = Table.Combine({#"Converted to Table", #"Converted to Table"}), #"Invoked Custom Function" = Table.AddColumn(#"Appended Query", "fn", each fn([Column1])), #"Expanded fn" = Table.ExpandTableColumn(#"Invoked Custom Function", "fn", {"Power Station", "Owner", "Nameplate Capacity (MW)", "Technology Type", "Fuel Type", "Service Status", "Region", "summary_bucket", "summary_status"}, {"Power Station", "Owner", "Nameplate Capacity (MW)", "Technology Type", "Fuel Type", "Service Status", "Region", "summary_bucket", "summary_status"}) in #"Expanded fn"Thank you so much for this solution ImkeF - absolute legend !!
It appears my error was in the syntax of the function, and explicitly declaring the FileURL as type text - using " " in the Web.Contents((".....") call.
The reason I haven't used your detailed steps is due to a change in the source files over the past 5 years. The source in the form of a table is only a recent addition from AEMO in the May 2019 release. Previously the data was only available from sheets - so the function needs to be robust enough to handle both formats.
This function now works - allowing further cleaning on the result from the function, and a similar method to be used to extract other sheet types.
let
ExistFn = (FileURL) =>
let
Source = Excel.Workbook(Web.Contents(FileURL), null, true),
#"Existing S & SS Generation_Sheet" = Source{[Item="Existing S & SS Generation",Kind="Sheet"]}[Data]
in
#"Existing S & SS Generation_Sheet"
in
ExistFn
