cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors