Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Team,
I have a sharepoint site, lets call it ABC. within it I have over 100 subfolders, and i would only like to access one sub folder called "data test" when connecting to PowerBI Desktop.
the site url itself is https://.sharepoint.com/sites/ABC
Since PBI desktop only lets you put the the site URL, when i load the site url, i have over 1000 of files, and cannot filter to that particular sub folder.
Is there a way to easily connect to one sharepoint subfolder? Any help would be appreciated.
Jerome
Hi all
I have an issue that people who don't have full owners access to the SharePoint cannot update the data query. Even if they have full access to the area the query is syncing files from they still cannot pass authorisation step in data query.
I need another team member to be able to update the file with SharePoint folder data queries not having full Owner's access to the site. Is it something that can be solved?
Thanks,
Dmitry
Very clear and easy to implement. But, what if the power BI user does not have access to the sharepoint main URL but just to a specific subfolder? In this case I guess he would get an error when updating the power BI model since he cannot access the sharepoint in first place? Is there any workaround in this case?
I am unsure of your specific use case, feel free to explain a little more.
Assuming you work in a business environment, with a dedicated sharepoint site, and you have been given the ability to create reports with Power Bi by your IT department. Then it would be probable that your IT department would grant access to sharepoint. I would raise the query with them and ask them for access.
You can check if you have access by signing into sharepoint using your business credentials.
Thanks for the answer. Briefly, I have access to main sharepoint, but the person that will update power BI will have access only to the subfolder (we do not want to give him access to the full sharepoint).
Hence I suspect he will get an authorization problem when retrieving data from the source (which is the main sharepoint).
Hope now it is clearer...
Unsure why the end user would be updating the report in your use case.
Power BI allows admins to publish reports within apps. The apps can be set to refresh periodically so the data is up to date. These apps can then be shared with end users so they can view the report.
The end user shouldn't need to refresh the data if the above has been implemented.
If you want your certain users to be able to create reports in Power BI you will need to give them more access, including to sharepoint.
Let me know if I've misunderstood something.
Thanks. That is the best should in my view. I have implemented it. Thanks again.
Thanks. That is the best solution in my view. I have implemented it. Thanks again.
Connect to SharePoint as usual, then in the formula bar change SharePoint.Files to SharePoint.Contents
This will change the pulled data view to a table allowing you to click into the word "table" on the left of the rows of data so you can navigate to the relevant directory.
Hi all,
I've resolved this issue. Use jer91899's method however for step 4:
> instead of filtering using the dropdown (which will not work if you have too many files), head to the section just above the filters that allow you to edit the query: type in: = Table.SelectRows(Source, each ([Folder Path] = "PASTE THE EXACT SHAREPOINT SUBFOLDER URL HERE" ))
Hope this helps
Hi
I have the same problem as a number of the posters in here.
Although I can connect as suggested above, all of my columns that are added to SharePoint are not pulling through (e.g. tailored fields).
How can this also be included in any other query?
Thanks
Hi @RichardTam , @Edson_Cruz94 , @Chrisjm15 , @Noivilbo -
I had this same issue today and was able to resolve by manually combining files in Power Query Editor (as noted by others, the wizard does not seem to include this functionality). Sharing the steps that worked for me below.
To add a SharePoint folder as a new source:
To convert an existing local folder path to a SharePoint folder path without redoing the queries:
Sorry, I don't have screenshots to share since the data in my example is proprietary.
Many Thanks, your explanation helped-me a lot!
Thank you for posting this. It has solved an issue I've been working on for hours!
Legend, this has been driving me insane and the solution worked perfectly 🙂
The "convert" solution leaves problems. The Helper querty still points point to the File, which is problematic if you need do a transformation later, or are hoping to do a data refresh from the service.
Thanks, this worked well!
hi jer91899,
Correct, these are the current way to get data, problem is lets say: https://sharepoint.com/sites/site belongs to a department, the department has a lot of folders and files.
Power BI has to go to the site, and read every single item within that site directory, before continuing step 4 which is to filter down to the folder you want
Common sense would say you can go to the sub-folder of that site directly, but that is not possible right now.
Combine with the fact that load anything from sharepoint takes way longer than other means. It is literally refresh and forget. Refresh, and forget you are working on it.
Ran into a similar issue where the solution I posted above wasn't working due to the load query failing.
The step-by-step approach to do this manually is outlined here https://learn.microsoft.com/en-us/power-query/custom-function, but in my case it was easier to create a temporary SharePoint site with a small number of files to build the query, and then go back and update the URL (set as a parameter) to point to the live site.
Hello,
I may have the problem solved by filters on the path of the files, for your reference if that would help:
I also have the problem that I want to query a sub folder under a Teams sharepoint which contains not only the to be queried files but also a lot of others, check the steps:
1. Still follow the steps to build a query to a sharepoint by Get data -> SharePoint folder, fill up the root path to the sharepoint, then the file list of all the files in the sharepoint is presented, with a column named as "Folder Path", in that column, each of the files' path are listed.
2. Quite simple now, filter this column by the sub-folder name that you are going to query, for me, I have to query into a subfolder under this sharepoint called "B1_Planning":
3. Then the follow ups will be quite normal operations in Power query. You will be finally get the query to the subfolder you filtered.
Thanks.
Your solution works for Power Query in desktop. I'm trying to port my desktop data sources to dataflows. I cannot browse the contents of the Sharepoint document library far enough to get to my data sources.
I suppose I could rename my folder to 00mydatafolder, but that would only crowd out the other folders and files used by other people in my department. It isn't really a feasible solution.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |