I have a company-wide On Prem Gateway available, and have successfully built reports from a Sharepoint folder where they refresh in app.powerbi.com. Our company has Pro licensing.
However, I have one Excel file with multiple worksheets to be modeled as tables also sourced in Sharepoint. It is updated weekly (i.e. file is replaced).
I simply do not know how to use the Get Data - Excel connector to “choose” the spreadsheet file as sourced in Sharepoint. I thought this article would get me over the hump ( https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/how-to-simply-connect-power-bi-to-excel-file-stored-on-sharepoint/ ), but cannot find the right format of my company’s Sharepoint “locations” to avoid errors.
To the best of my understanding, here is how I link to the Excel file maintained in Sharepoint:
https://companyXYZ.sharepoint.com/:x:/r/sites/Super-Cool-Stuff/Shared%20Documents/Cohort%20One/Data%20Sources/PowerBI_Superstore_Dataset.xlsx?d=w964a53395f1e404f8019315e5344c421&csf=1&e=bNW3St
Whatever magic it takes to get Power BI (or Power Query) to recognize that path/file, I’ve yet to find.
In the end, if a Sharepoint source has to be added to the Company On Premise Gateway in order to refresh published reports from the Power BI Service, that’s reasonable.
Do you have a recommended approach here where I hopefully can translate your steps into how I identify my Excel source file in Sharepoint?
Thank you,
Kevin
Hi Kevin,
I’ve find this method works perfectly for files in SharePoint Online
To connect to SharePoint files on prem however, see if this resolves your issues.
Hi @kkieger, we’ve noticed that no response has been received from you since the 28th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
HI Melissa,
The information you provided makes sense and was helpful. When I attempt the recommended approach with the Get Data - ‘Web’ connector from Power BI Desktop, after providing the URL for the file (removing ?web-1), I do not get a credentials dialogue window, but rather an error… Unable to Connect… Details: “Access to the resource is forbidden.”
What I recall is that in previous attempts, I likely entered some credentials as the video tutorial shows when trying the ‘Web’ connector, but now it is no longer an option.
I do not know of further actions to take.
Ok we’re reaching the point where it becomes increasingly harder to give any kind of advise…
Think the best course of action is to check the basics:
- Have there been any recent changes to permissions or security by IT?
- Check if your Data Gateway is up to date and test all connections.
- Have you tried adding the Sharepoint site to the Gatewaycluster?
Hi @kkieger , just a friendly reminder, asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum