accessing a file embedded on a web page

[wrwillits]

Can I access a file throught Power Query that is embedded on a web page? 

I am trying to set up a link to an Excel file that is embedded on a web page. I need to link to the historic and current oil prices from a US Energy website ( https://www.eia.gov/dnav/pet/pet_pri_spt_s1_d.htm ). The isue is that the data is contained in an Excel file that is embedded on their web page, and the only way I'm curerently able to assess it is to manually download it and then manipulate it in Power Query. Instead I want to automatically open the file and load the data into Power Query when I Refresh my PowerBI model. Is there any way to get Power Query to automatically go out an open the file and read the data for me?

[v-juanli-msft]

Hi @wrwillits 

As tested, i can connect to this page with "Web" connector, with some transformations in Power query, it is possible to get data as below

Then add to a matrix, it shows as below:

But it doesn't update newest data to Power BI as i change the "period" from the web or any data updates there.

I think we may find a API for that web page and use it in Power BI.

I will update my answer as soon as i find the solutions.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wrwillits]

Hi Maggie,

Thanks for your reply. As you will see in my response to Artemus, I can't connect to the file using the Web connector in PowerBI because it's telling me that I don't have the proper "Access Database Engine 2010 Access Database Engine OLEDB", enen though I have the latest version of Office 365 installed (64 bit). 

Also, I would be very interested in an API if it is available.

Thanks,

Warren

[artemus]

Web.Contents is a pretty core function in Power Bi. You can't do much without it.

When exactly do you get this error? Can you create a blank query and copy/paste the query I posted previously?

[wrwillits]

I get the same error message.

[artemus]

Hrm... I wonder why it works if you download it locally first? You are sure if you load it locally you do not have this problem?

[wrwillits]

I have no problems loading it locally. I've been using the data in the below BI model for the past several months (downloading it manually each moth and then refreshing the model).

[wrwillits]

And here's confirmation that I'm running Office 365, 64 bit.

[artemus]

Hrm.. I'm at a loss to what is happening here. I may be trying to load it in a different way since it is on the web.

Could you try:

= Excel.Workbook(Binary.Buffer(Web.Contents("https://www.eia.gov/dnav/pet/xls/PET_PRI_SPT_S1_D.xls")), null, true)

If that doesn't work, see if adding Binary.Buffer when loading your local file works:

= Excel.Workbook(Binary.Buffer(File.Contents("c:\MyDownloadFolder\PET_PRI_SPT_S1_D.xls")), null, true)

If the above doesn't work then it indicates there is something special happening since it is an excel file.

Also, try and running your query in Excel and see if that works (Open a new Excel spreadsheet, Use Get Data, open query editor, then select all your queries in Power Bi and Control-C, then Control-V them into Excel).

[artemus]

= Excel.Workbook(Binary.Buffer(Web.Contents("https://www.eia.gov/dnav/pet/xls/PET_PRI_SPT_S1_D.xls")), null, true)

[wrwillits]

Had the same issues in PowerBI.

The crazy thing, though, is that I am able to access the data using Excel.

[wrwillits]

And I only had to use the Web Connector in Excel.

[artemus]

So, just to be clear:

= Excel.Workbook(Binary.Buffer(File.Contents("c:\MyDownloadFolder\PET_PRI_SPT_S1_D.xls")), null, true)

gave the same error in Power Bi, as this would indicate that the fact the document is loaded from the file system is what causes it to correctly load.

[wrwillits]

No, it gave me the following error:

[artemus]

Sorry, I mean, replace the path with the path to your downloaded file

[wrwillits]

Sorry, my fault.

Here's what I get now:

[artemus]

Alright, looks like you will need to install this package:

https://www.microsoft.com/en-gb/download/details.aspx?id=13255

Get the x64 version.

[wrwillits]

I get the same message when I try to install the 64 bit version, even though my installed Office 365 is 64 bit:

[artemus]

Ok... how about trying to install this one instead:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

[wrwillits]

I think we have finally figured out the issue! Was able to load the data into Power Query.

I still do not understand wehy I needed to load a new version of Access engine to make it work though.

[wrwillits · Accepted solution]

Thank you for all your help!

[artemus]

1. Do Get Data

2. Choose the Web option

3. Type in the url

4. Choose anonymous authentication.

5. Hit load

Or add a new blank query and type inb:

= Excel.Workbook(Web.Contents("https://www.eia.gov/dnav/pet/xls/PET_PRI_SPT_S1_D.xls"), null, true)