Connect directly to a specific SharePoint Folder - not the entire site

[zcholla]

PROBLEM:
I am workin on a few projects for a specific site in our sharepoint tenant. This site is extremely lare with over 3.5TB of data. I need to run a few reports that are specific to individual folders in the site. The problem I have is that when I connect through Power Query, it forces the entire site contents to be loaded and does not allow me to hit the specific folder without first hitting the entire sharepoint site. So this mean I am first loading every folder on the sharepoint site and then filtering to a folder. The causes the reports and refreshes to take an excruciatingly long time to load and refresh because it is not just refreshing the folder but the entire site data in the query. 

REQUEST: Is there a way to bypass connecting to the entire site and load directly into the folder I need?

[Thomas_A]

@zcholla

I have the same problem. Did you ever find a solution.

Thanks, Thomas

[Anonymous]

it will be awesome if there is a way to connect directly to a folder, instead of going trough the entire site first, but unfortunately there is no workaround yet, or i will be more than happy to know if there is one ! 🙂

[slorin]

Hi

May be with this solution

https://chandoo.org/wp/connect-sharepoint-files-from-powerquery/

Stéphane

[krish57]

Hi @zcholla , I am also facing this issue since 1year and unable find the solution. I tried with ondrive also with no success. Kindly post the solution if you solved.

[JBMartin]

Hi @krish57 

Check out vijay verma's solution in this thread - its works perfectly. The key is to change the Sharepoint.Files command to Sharepoint.contents and then navigate down through your folders to the one you are targetting.

[JBMartin]

Hi @zcholla I'm experiencing exactly similar issues to those that you describe - wondering if you ever found a workable solution? I've been trying to add tags in Sharepoint as a means of filtering but nothing working so far.

Thanks - Barry

[v-jingzhang]

Hi @zcholla

Once you connect to a sharepoint site and see files in the preview window, click "Transform Data" rather than "Combine". This will bring you to Power Query Editor. 

You will see a Folder Path column there. 

Click Down arrow on Folder Path column header to filter rows based on folder path. You can filter the specific folder you want to connect, then remain only files in this folder.  

Additionally, I found a complete guide blog Connect Power Query to a whole SharePoint folder 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[zcholla]

Sorry your soluion is actually what I am trying to avoid doing. The probelm I have by doing it this way, is you first load the entrie site and then filter to a folder. When you refresh you refresh the query for the entire site, in my case that is millions of rows. I only want to hit and refresh the specific folder to avoid that large data refresh. 

[Vijay_A_Verma]

You can avoid it by this way

1. Create a blank query in Power BI.

2. Paste the below code there

let
    Source = SharePoint.Contents("https://mycompany-my.sharepoint.com/xyzabc/", [ApiVersion = 15])
in
    Source

3. Replace https://mycompany-my.sharepoint.com/xyzabc/  with your company's sharepoint root URL. (You already know this as you are able to connect it using Sharepoint.Folders.

4. Now, first column will have many tables. Click the table which contains your folder (this should be at the bottom). You may be required to click that column mulitple times depending upon how much down your folder is.

5. Once you reach to the required folder, do the required transformations

[KatrinaCole]

Yes! This works for me also.

[JBMartin]

This solution works beautifully for me. Navigation steps do require a little work to modify the on-premises folder queries to start at the higher root folder but once done (and all transform functions need to be changed as well), it runs really quickly (way faster than against on-prem via the gateway). 
The default SharePoint.Files method by comparison is a disaster. Thanks Vijay

[zcholla]

Sorry your soluion is actually what I am trying to avoid doing. The probelm I have by doing it this way, is you first load the entrie site and then filter to a folder. When you refresh you refresh the query for the entire site, in my case that is millions of rows. I only want to hit and refresh the specific folder to avoid that large data refresh. 

[Syndicate_Admin]

@Syndicate_Admin , in the example in the below Microsoft article, you can connect directly to a SharePoint folder. Check it out: https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder#determine-the-site-url

[zcholla]

Sorry your soluion is actually what I am trying to avoid doing. The probelm I have by doing it this way, is you first load the entrie site and then filter to a folder. When you refresh you refresh the query for the entire site, in my case that is millions of rows. I only want to hit and refresh the specific folder to avoid that large data refresh.