Connect to Excel without access to the Sharepoint site/group (using share-url)

[fenixen]

Hello

Is it possible to connect to Excel files in sharepoint using sharepoint-generated Share URLs?
I want to connect to archiving without having access to the entire site or even folders on the site, just the document.

I tried to paste the URL into the web connector without luck.

It's possible?

[AllisonKennedy]

Is the data public? Is the Share link set to public? What do you what to do with this report and what refresh capabilities does it need to have? Do you have a data gateway?

Generally speaking, Power BI reports require authentication for most web data sources to refresh automatically, so that's why all my questions about what you need to do with the report.

[fenixen]

Hi 

Share link is not public, I've chosen the option:  People in "company" with the link
I then paste the link into the web-connector in Power BI. 

In the autentication dialogue i choose organizational account and sign in. It responds that "We couldn't autenticate you with credentials provided". Which is strange because the URL points to a file on a sharepoint site i own. The URL works fine in Edge, takes me directly to the file. 

I dont need any scheduled refresh capabilities, for this report I can refresh manually through Power BI desktop. 
(I have both server and personal data gateways, but I cant even get this working in Power BI desktop). 


I get the feeling that Power BI actually dont support getting excel-files using share-url from Sharepoint?

[AllisonKennedy · Accepted solution]

Ok, easy. You can either use the Get Data from Excel option or SharePoint folder option, but the URL you need to use is just the root URL of the SharePoint document library, not the file itself. You'll have to use Power BI to navigate to the file once you paste the root URL. 

So for example if the link looks like this: 

https://aceapr20.sharepoint.com/:x:/s/ATeam/EUfgYnM0_EZFkQt-LlYpY_EB1licC53mMujr8p5RvT8dfw?e=IRBGmu

To connect to the SharePoint folder, just use:

https://aceapr20.sharepoint.com/ATeam/

Then click Transform the data. Then you'll need to click the Binary link next to the specific Excel file you want. 

If you only need one Excel file, then you can choose Get Data from Excel.

You will need to open the file in Excel Desktop, click File > Info and copy the link under the filename: 

https://aceapr20.sharepoint.com/sites/ATeam/Shared%20Documents/General/Hot%20work%20checklist%20.xls...

then you'll need to take only the root of it, so: 

https://aceapr20.sharepoint.com/sites/ATeam/Shared%20Documents/General/Hot%20work%20checklist%20

The next part can be tricky if you don't have SharePoint access in your file explorer, so one way to cheat the system without writing your own code is to save a copy of the file on your local desktop or PC. 

Get Data > From Excel, choose the copy of the file from your PC. 

Transform Data.

In the Query Editor, select the settings cog next to the Source step and paste that SharePoint Excel URL into the browse box:

https://aceapr20.sharepoint.com/sites/ATeam/Shared%20Documents/General/Hot%20work%20checklist%20

Then click ok. You'll see an error about needing to provide credentials. That's when you sign in with your organizational account. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv