Power Query Merge data stored in separate files in separte folders

[Claudine B]

Hi,

I have the following

1. 35 workbooks, that contains 1 particular sheet which contains data in a named range that I would like to merge
2. the 35 workbooks are stored in subfolders (each country has his own folder) on a SharePoint environment

what is the recommended way to merge this data?

I am exploring the book M is is for (DATA) MONKEY but struggling to find the answer on this Q

thanks
Claudine

 

[Ken Puls]

Hi Claudine,

To get data from Sharepoint, you need to connect using the Sharepoint connectors. I don't think we actually included those in the book, but here you go:

Sharepoint: Get Data --> Sharepoint Folder. I believe that the URL you need should be formed along the lines of this, although you may be able to drop the GroupName part out:
https://<siteurl>/sites/<groupname>

Sharepoint on Office365: Get Data --> Sharepoint Folder. The URL will be formed like this:
https://<tenant>.sharepoint.com/sites/<groupname>

OneDrive for Business (on Office 365): Get Data --> SharePoint Folder. The URL will be formed like this:
https://<tenant>-my.sharepoint.com/personal/<email*>

Where the email replaces all @ and . characters with _

HTH,

 

[Claudine B]

Hi Claudine,

To get data from Sharepoint, you need to connect using the Sharepoint connectors. I don't think we actually included those in the book, but here you go:

Sharepoint: Get Data --> Sharepoint Folder. I believe that the URL you need should be formed along the lines of this, although you may be able to drop the GroupName part out:
https://<siteurl>/sites/<groupname>

Sharepoint on Office365: Get Data --> Sharepoint Folder. The URL will be formed like this:
https://<tenant>.sharepoint.com/sites/<groupname>

OneDrive for Business (on Office 365): Get Data --> SharePoint Folder. The URL will be formed like this:
https://<tenant>-my.sharepoint.com/personal/<email*>

Where the email replaces all @ and . characters with _

HTH,

Hi Ken,

thanks

I retrieved the filepath & I figured out that I could access all files via From File, From Folder, with the link to the highest level
I put a filter on my text names, which contain a fixed part
then I added custom column =Excel.Workbook([Content]) to retrieve 1 particular sheet , again via Filter on name
that works, it's quit slow but I saw no other solution for now

Kr Claudine

 

[Ken Puls]

That it probably going to be about as fast as it will go. With data stored remotely in a non-database setup, you're kind of stuck on both retrieval speed, as well as locally processing the data in Excel.