Hello,
my current situation is as follows:
- I have 7 Excel workbooks in MS Teams folders
- every workbook contains 4 different tables
- I want to create an overview by importing and appending the tables
- if I calculate correctly this comes up to 28 single queries
- and then 4 additional ones for consolidation
Sounds not smart too me.
So, the question is: Is there a smarter way to get this job done?
Any help is very much appreciated.
Best regards Martin
Hi Martin,
Indeed, not smart 🙂
Go to session 2.05 for getting files from a folder and watch the video below the first video (on the same page) you will see the process for the old version of Excel. You want to use this process to get the data from multiple sheets in multiple files.
Note: The buttons in the Combine Data dialog that appears after you enter the folder path will be different to those you see in the video. Here you want to choose 'Transform' i.e. not Load or Combine.
Then you'll be able to add a column with the Excel.Workbook([Content], true) formula.
Note: the file path for the folder should be the synced folder on your hard drive, not the SharePoint folder URL that Teams saves the files to.
Mynda
Hi Mynda,
and many thanks for your answer.
It worked for me. I now finally end up with just 4 queries for the different table types (employees; production output etc. Just duplicated the first one and adjusted the filter accordingly).
(and yes: I access the files via synched folders, cause direct access to Teams folders is not yet possible (barely to understand, because finally they are 'only' Sharepoint folders...and there is already a connector, but maybe some day soon ;-)))
Best regards Martin
