Forum Discussion
Get excel file from URL as custom function
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"
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