Forum Discussion
Connecting to Excel on SharePoint
Hi
What is the best way to connect to an Excel file on a SharePoint site?
I had originally connected using the standard Excel connector. This connected to the Excel file ok, but when the data changed in the Excel file refreshing in Power BI did nothing which makes me think this is not the best way to connect to SharePoint?
I tried SharePoint folder and SharePoint list and could not figure out how to make this work - I could only connect to the root URL and couldn't figure out how to get to my sub folder? Is there any documentation I can follow?
Thanks
Rach
Rachey The trick is to just get the right URL path, its worth the effort. Here is a blog tutorial -> https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/
Hi Rachey,
It seems that you have problems with connecting excel on Share Point.
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, you could have a reference of this similar thread.
In addition, you could have a look at the blog below about the ways to conncet excel filed in SharePoint.
If your excel is online, you don't need to configure the on-premise Gateway, if your excel is local file, you need to configure the on-premise Gateway.
Best Regards,
Cherry
4 Replies
Rachey The trick is to just get the right URL path, its worth the effort. Here is a blog tutorial -> https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/
Thanks Anonymous. This helped me find the correct root :)
Unfortunately I'm still having problems, but I'm not sure if this is a Power BI issue or a SharePoint issue. When I load the data into the query editor it doesn't show all the folders, and weirdly the latest date modified is July 2016. Is there a capacity limit to the number of folders that it can locate?
I'm going to have to continue with the Excel connector till I can resolve this SharePoint issue. Just for clarity, I'd like to understand:
* Is the SharePoint folder connector considered better practice? And does this mean I won't need to create an enterprise gateway to refresh dashboards in Power BI online?
* If I use the Excel connector to the SharePoint folder will I need the enterprise gateway?
Thank you. Very much appreciate the help.
Rach
Rachey I don't recall the details off the top of my head, but I believe there are authentication issues with having the Excel file in the cloud and not being able to connect via Oauth2.... I forget at the moment. I do recall having to flip a solution to use the SharePoint folder instead. It sounds like you have a path, but not the right level. There are no restrictions on what it should see as you describe.
