connecting to sharepoint sub folder

[Anonymous]

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

[DmitryC]

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

[vstefano]

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?

[Controla]

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. 

[vstefano]

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...

[Controla]

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. 

[vstefano]

Thanks. That is the best should in my view. I have implemented it. Thanks again.

[vstefano]

Thanks. That is the best solution in my view. I have implemented it. Thanks again.

[Controla]

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. 

[Sachin_Murugan]

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 

[AndyBlake]

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

[jer91899]

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:

1. Click Get Data > SharePoint folder
2. Enter the root of the SharePoint site (example: https://sharepoint.com/sites/site)
3. Click Transform Data (ignore the long list of files)
4. 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.
5. 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. 
6. Continue transforming data as needed.

To convert an existing local folder path to a SharePoint folder path without redoing the queries:

1. Open Power Query Editor and select the Source (first step) of the primary query (NOT the helper query).
2. 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.
3. 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.
4. 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.

[swilder]

Thank you for posting this. It has solved an issue I've been working on for hours!

[DamianKelly]

Legend, this has been driving me insane and the solution worked perfectly 🙂

[jusTodd]

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.

[Edson_Cruz94]

Thanks, this worked well!

[RichardTam]

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.

[jer91899]

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.

[raolei0721]

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.

[Edson_Cruz94]

I have the same problem... looking for any option to solve that.

[TomMartens]

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,
Tom