Forum Discussion
Loading every workbook inside a folder using one main file on Sharepoint
- Nov 07, 2021
Otherwise it doesn't work. Your URL to connect is
https://COMPANYNAME.sharepoint.com/sites/odms
Transform returned and in Power Query editor you shall filter this column
to keep only needed folder.
If that's Excel for web afraid that doesn't work.
In Excel Desktop I'd use From SharePoint Folder connector for Power Query to summarize raw data and ignoring already created PivotTables.
- kheldarNov 05, 2021Iron ContributorWell, the files are on Sharepoint but we launch them using the desktop app. Could you please point me towards how to do it? Because I tried and the data appears in all weird formats and stuff. I want it to load as is with values.
- SergeiBaklanNov 05, 2021MVP
It depends on your how other files are structured, do they have exactly the same structure or not and what you'll be query.
In general you may start Data->Get Data->From File->From SharePoint Folder using root site URL to connect. It will show all files on the site with their paths, filter only on path where your files are. Remove all columns but Content and expand it. It will be small wizard to do the transformation based on sample file (first one in the folder is selected as sample). Do the transformation, after that it will be applied to all files and result combined.
If something more complex you may start as above and adjust additionally transformation and related queries.
- kheldarNov 06, 2021Iron Contributor
Thank you for your response.
I'm trying but the root of the sharepoint folder doesnt yield the results I want.
The folders are in Shared Documents folder. Could that be the issue?The URL of the folder I want to access to is as below but excel doesn't accept it.
https://COMPANYNAME.sharepoint.com/sites/odms/PROJECTNAME/PROJECTNAMECITYts/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2Fodms%2PROJECTNAME%2FPROJECTNAMECITYtsts%2FShared%20Documents%2FBusiness%20Planning%20%2D%20Account%20Planning%2F3%2E%20Resource%20Planning&viewid=37aeaabe%2Dbe64%2D4881%2Db5db%2D01fbadf9afec