Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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?
I get the same error message.
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?
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).
And here's confirmation that I'm running Office 365, 64 bit.
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)
Had the same issues in PowerBI.
The crazy thing, though, is that I am able to access the data using Excel.
And I only had to use the Web Connector in Excel.
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.
No, it gave me the following error:
Sorry, I mean, replace the path with the path to your downloaded file
Sorry, my fault.
Here's what I get now:
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.
I get the same message when I try to install the 64 bit version, even though my installed Office 365 is 64 bit:
Ok... how about trying to install this one instead:
https://www.microsoft.com/en-us/download/details.aspx?id=54920
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.
Thank you for all your help!
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |