Forum Discussion
connecting to sharepoint sub folder
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
28 Replies
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:
- Click Get Data > SharePoint folder
- Enter the root of the SharePoint site (example: https://sharepoint.com/sites/site)
- Click Transform Data (ignore the long list of files)
- In the Power Query Editor, filter the Folder Path column to only include the desired folder (search for the folder name and select it). The table should now have one row for each file in the SharePoint folder.
- On the Home tab of Power Query Editor, click Combine Files. If this button is grayed out, try clicking on another query then come back to this one, and then it should be active. This will create the same parameters and helper queries created by the wizard when using a local folder.
- Continue transforming data as needed.
To convert an existing local folder path to a SharePoint folder path without redoing the queries:
- Open Power Query Editor and select the Source (first step) of the primary query (NOT the helper query).
- Change Source path from = Folder.Files("C:\Users\path\data\") to = SharePoint.Files("https://site.com/sites/site", [ApiVersion = 15]). If this string does not work, you can find out what your string should be by creating a test connection to a SharePoint folder using the steps above, then copying the string that is generated to all queries that need to be moved.
- Filter the Folder Path column to only include the desired folder (search for the folder name and select it). If it's not possible to filter for the unique folder directly (if many results are returned), select only one folder and manually edit the filter text in the text bar of Power Query Editor to point to the desired folder.
- Query should now refresh from SharePoint instead of from the local folder.
Sorry, I don't have screenshots to share since the data in my example is proprietary.
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 wantCommon 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.
Thanks, this worked well!
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.
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.
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.
Connect to SharePoint as usual, then in the formula bar in the query editor 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 then draw down on the data.
You can also do this for parent folders with subdirectories.
- Hey,
unfortunately this not possible.
You have to use the path field to use text filter that specifies your subfolder.
Wondering why you can't filter, because of the amount of the files?
Regards,
TomHi Tom,
Yes, there are too many files in that site, so it only lets me filter to the first 1000.
I am trying to reach site/ABC/x/x/x/x/data test/
within that, is 4 excel files. the goal is to add files each month so i can refresh it easily.it seems that the only way is to have a local file path and refresh it on 1 computer?
Hi Tom
I am having the same probleme as Jerome (and probably as many other people). I fi get this right, you are saying that one can only connect to the root SharePoint folder? If this is the case, i don't see the value of connecting Power BI to Sharepoint as:
1. The files organisation becomes a mess
2. As Power BI only loads 1000 files, there is very little probability that we will be able to find the files we need in this subset.
Am I missing something? If yes, can you give us guidance on how to connect Power BI with files on a Sharepoint subfolder?
Many thanks
Daniel
+1 on this , we have exactly the same issue. There must be a stable/efficient way to connect PBI to sharepoint, given both are microsoft?
FYI for OP - workaround that I'm using, is creating a new sharepoint library, which only contains a limited number of files that need to connect to powerBI. And for you, worst case, you could make a microsoft automate worklow that copies the files in certain folders from your current sharepoint library to the new one overnight?
I have the same problem... looking for any option to solve that.
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
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...
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
