Forum Discussion
Sharepoint Connector - Combine Data across multiple Excel Files
I am trying to create a summary excel file that pulls data from identical tables stored across multiple excel files. I have used Power Query and have gotten this to work when the excel files are stored on a local onedrive folder, but am running into a roadblock when trying to access files stored in a Sharepoint Folder.
Unlike when you can browse windows explorer, I cannot get to the level that the excel files are stored on Sharepoint.
Here is the example of the sharepoint location of these excel files:
https://companyname.sharepoint.com/sites/sitename/ProductDevelopment/ProjectInformation/Project Manager Files/Laurie's Test Project Folder
I've tried using just the site root, but I cannot for the life of me, get to the subsite/subfolder level. I have tried Power Query and Power BI. I've tried Sharepoint Folder connection, SHarepoint List connection, Web connection, OData...
Am I out of luck?
Thank you. I understand this, but the problem is my files (data) is not at the top subsite level, it is within a document library, and then two subfolders within that library. The problem, I believe, is that due to the enormous amount of files on our company sharepoint site, I reach a data limit and I cannot even get to the point where it finds the folder path that my files are located in (to be able to filter).
My solution that I have come up with is to create a 3rd tier subsite. Now, I can direct the query to go exactly to this subsite where my data will be located.
I find it frustrating as it seems this is a common complaint, but perhaps someday there will be a fix to be able to navigate directly to certain subfolders within Sharepoint.
3 Replies
Hi Anonymous
To connect to Excel Files hosted on a SharePoint folder with Power BI,
From the Get Data experience, let’s find the option for “SharePoint Folder”.
This option will only require a text string which would be the URL for a SharePoint site.
In order to use the correct URL, we need to be in the folder of the data that we’re trying to get and check the url that our browser shows.
If it has the if it starts with the format of https://<site address>/sites/<sitename>/ then we need to use that url, otherwise we use the much simpler https://<site address>
Best Regards
Maggie
Thank you. I understand this, but the problem is my files (data) is not at the top subsite level, it is within a document library, and then two subfolders within that library. The problem, I believe, is that due to the enormous amount of files on our company sharepoint site, I reach a data limit and I cannot even get to the point where it finds the folder path that my files are located in (to be able to filter).
My solution that I have come up with is to create a 3rd tier subsite. Now, I can direct the query to go exactly to this subsite where my data will be located.
I find it frustrating as it seems this is a common complaint, but perhaps someday there will be a fix to be able to navigate directly to certain subfolders within Sharepoint.
- can you elaborate how you create subsite, i am facing similar issue while accessing the sub folder in sharepoint
