Forum Discussion
accessing a file embedded on a web page
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?
Thank you for all your help!
21 Replies
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)
Hi Artemus,
I have tried both of your options but keep getting the error that states that I need a 64 bit version of "Access Database Engine 2010 Access Database Engine OLEDB" to read this file. I'm running a 64 bit version of Office 365 Access already. Also, I tried to download a version of "Access Database Engine 2010" thinking that maybe I had a older version, but the system told me that I'm running a "32 bit Office Product and need to upgrade to a 64 bit version". I double checked my Office suite and confirmed that I'm running a 64 bit version.
What's going on?
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.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
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?
