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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
wherdzik
Helper III
Helper III

Combine Microsoft Excel Worbooks in SharePoint Folder Environment- Combine Specific Sheet in Excel

I had a powerquery that worked well when all of the folders were on the network drive but now our organization moved everything to SharePoint.  I had a critical process where I connected to a folder which had budgets in separate folders.  The query would get all excel files with these budget folders containing  the name BUDGET 2024 and it would take the PHYS sheet in each of these workbooks and combine them together.  I tried replicating this using get from SharePoint folder but it doesnt want to work with this address below.  The address has been changed for security purposes but I wanted to show you what this address looks like when I get to the folder I need to be in SharePoint. 

 

https://sharepoint.com/sites/Leadership/Shared%20Documents/Forms/AllItems.aspx?RootFolder=%2Fsites%2...

 

The only way I can get it to work is if I change the address to the following but I cannot get to my budget folders. 

= SharePoint.Files("https://sharepoint.com/sites/Leadership/

 

If I chagne this to the following source by getting contents I can click and get to the budget folders but I dont know how to combine them at that point.

= SharePoint.Contents("https://sharepoint.com/sites/Leadership/

 

The files are contained in https://sharepoint.com/sites/Leadership/Shared Documents/Ops and Managers/Operating Budget/2024_Operating_Budget/

 

If I try to type that in to Powerquery into this address, it doesnt like it. 

wherdzik_0-1699299585255.png

Can someone guide me? This is a new space for me.

 

Thanks.

1 ACCEPTED SOLUTION
j_ocean
Helper V
Helper V

Connect tot he site, then filter down to the folder. From there it should work the same as before.

View solution in original post

2 REPLIES 2
christinepayton
Super User
Super User

Here you go - I did a video on how to change your source from the folder connector to SharePoint folder here, it goes through it step-by-step: https://youtu.be/5SFxHffE2TA?si=kYOluPF-Kiy91i_k

j_ocean
Helper V
Helper V

Connect tot he site, then filter down to the folder. From there it should work the same as before.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors