Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
zcholla
Regular Visitor

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

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?

15 REPLIES 15
Thomas_A
New Member

@zcholla

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

Thanks, Thomas

Anonymous
Not applicable

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
Super User
Super User

krish57
New Member

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.

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
Regular Visitor

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
Community Support
Community Support

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. 

vjingzhang_0-1655369969082.png

 

You will see a Folder Path column there. 

vjingzhang_1-1655370361154.png

 

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.  

vjingzhang_2-1655370515199.png

 

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.

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
Super User
Super User

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

This works great! Thank you.

Yes! This works for me also.

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

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

@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

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. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.