Get excel file from URL as custom function

[pat_energetics]

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 

https://www.aemo.com.au/Electricity/National-Electricity-Market-NEM/Planning-and-forecasting/Generat...  . 

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

[ImkeF · Accepted solution]

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"

[pat_energetics]

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