Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.