cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

2 REPLIES 2
Super User
Super User

Re: 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"

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Re: Get excel file from URL as custom function

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

 

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 139 members 1,665 guests
Please welcome our newest community members: