I’m looking for a way to merge Excel sheets from different folders.  Situation: Excel files are added to a SharePoint each day via email delivery to a sub-channel.  SharePoint automatically puts them into a folder based on the delivery month… so in the future, there will be new folders created automatically.

I’ve tried to use Power Query to pull all the files from the parent folder– it’s utterly stumped, and won’t read the files themselves; it meticulously pulls data from the email (.eml) files, and only pulls the file properties from the Excel (.xls) files.  

How do I get it to read the .xls files for data when they’re in different folders– and more folders will be added over time?

​I’m looking for a way to merge Excel sheets from different folders.  Situation: Excel files are added to a SharePoint each day via email delivery to a sub-channel.  SharePoint automatically puts them into a folder based on the delivery month… so in the future, there will be new folders created automatically. I’ve tried to use Power Query to pull all the files from the parent folder– it’s utterly stumped, and won’t read the files themselves; it meticulously pulls data from the email (.eml) files, and only pulls the file properties from the Excel (.xls) files.   How do I get it to read the .xls files for data when they’re in different folders– and more folders will be added over time?  Read More

​