Opening an Excel Spreadsheet from a SharePoint site?

[Anonymous]

Hello all,
I hope everyone is staying safe and well!
I have a problem, and it is turning into one of these that gets worse the more I research it. Opening an Excel Spreadsheet stored on our Sharepoint site. 
Some claim you an just select data source Excel and open it, other say use the Web data source, and now I am investigting using the Sharepoint Folder data source. Needless to say, I have not gotten any of them to work.

Can someone please tell me how to open an Excel sheet stored on a microsoft.com SharePoint site?

[Anonymous]

Hi @Anonymous ,

First, in Power BI Desktop, there are three options for connecting to SharePoint. Here is a post that introduces the difference between the three, please refer to:

https://community.powerbi.com/t5/Desktop/Difference-between-Sharepoint-list-vs-Sharepoint-Online-List-vs/td-p/691634#:~:text=First%2C%20Sharepoint%20List%20and%20Sharepoint,the%20files%20stored%20in%20Sharepoint.

The following is my process of connecting to Excel file in SharePoint to Power BI Desktop.

Open the Excel file in the browser, then select Info to click Open in Desktop App.

The Excel file will be opened in Desktop App, select Open to copy path to clipboard.

Remove the ?web=1 string at the end of the link so that Power BI Desktop can properly navigate to your file, and then select OK.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Stephen, but if you look at my last post, I had got this working in the full Desktop client, but I really need it to work in the Reporting Server Desktop client, but this process fails there, and I don't know if it is a bug in that version of Desktop, or if Reporting Server cannot support it?

[amitchandak]

@Anonymous , You need to do a small change in URL for SharePoint and one drive

refer to this: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links

You can also check: excel in sharepoint
https://community.powerbi.com/t5/Desktop/Import-Data-from-an-Excel-sheet-in-SharePoint/td-p/47223
https://www.youtube.com/watch?v=3GIz50pftZ0
https://www.youtube.com/watch?v=gJF2e_43FRY
https://whitepages.unlimitedviz.com/2018/07/excel-files-power-bi-desktop-sharepoint/

[Anonymous]

Thanks for that Amit,

The first option did not work, as unlike the OneDrive, it never asked for credentials, so just gave me this:

The next interesting post is Curbal's video, but it appears her video is so old that the process has changed in BI and I cannot change Tables to files, as she does, but I do get a list of tables, but donot have any way of switching to files, as I am still stuck in the wizard, and not in the editor as she is.

The last link does not help, because if you keep the folder "sites" after your sharepoint address, it gives the error you get if you don't strip the rest of the path out.

I would love the top one to work, but I just don't get asked for credentials.

Really frustrating now.

But thanks for your answer and all the links.

[Anonymous]

I have discovered the issue. Amit solution works in the default Desktop version of Power BI, but I was trying to build this in the Reporting Server version with the black icon, which was last updated in May 2020, and it fails with this version. So I have started building it in Standard Desktop, then saved it, and tried to open it with Desktop RS and it is not compatable. So the question to be answered now is if it is just the case of Desktop RS needs a revision, or can I simply not add spreadsheet from Sharepoint at all in Reporting Server?