Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pat_energetics
Advocate II
Advocate II

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
ImkeF
Super User
Super User

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors