Forum Discussion
Loading every workbook inside a folder using one main file on Sharepoint
Hello,
Is it possible to load every workbook inside a folder using one workbook. The workbooks each contain 9 sheets made out of raw data, pivot tables and one sheet that is dedicated to summarize data from different pivot tables. I actually only want to load that one particular sheet. I tried messing with Power Query but couldn't get it done. Please note that the files will be on Sharepoint.
Any help would be most appreciated it.
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.
7 Replies
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.
- Well, 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.
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.
