Loading every workbook inside a folder using one main file on Sharepoint

[kheldar]

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.

[#2]

@kheldar 

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.

[#3]

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.

[#4]

@kheldar 

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.

[#5]

@Sergei Baklan 

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 

[#6 · Accepted solution]

@kheldar 

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]

Thank you!!!! It worked.

[#8]

@kheldar , glad to help