CODEWITHSUNDEEP
excelsharepointpower-automate
EricP
EricP

Reputation: 5

Combine multiple Excel files stored in Sharepoint to one using Power Automate

I'm new with Power Automate and I'm trying to combine existing Excel files stored in Sharepoint folder (eg folder "data" in my Document library) in a single one (such as an Excel master file). The trigger is either when a new Excel file is uploaded to Sharepoint or an existing one is modified. All files have the same structure (eg column's name), the only thing is the headers start at row 10

enter image description here

Upvotes: 0

Views: 1262

Answers (1)

CHill60
CHill60

Reputation: 2033

Posting as a solution as I'm running out of characters in the comments.

Firsly, I suggested Power Query not Power BI.

It is a simple matter to get it to read all the files in a folder, regardless of naming conventions, and merge them into a single worksheet.

Each time it runs it will pick up any new files and the data in them. So consider housekeeping over time and "age" of data.

You can have the queries refresh when you open the master file, but it might be better to have some VBA in Workbook_Open() that refreshes the query then saves the workbook. You must have 'Run as Background Query' turned off if using Power Queries in VBA. This is to ensure that control only returns to VBA when the query has completely finished. The same might be true of Office Scripts.

There are plenty of resources across the Internet that will give you a step-by-step guide to implementating an action via Power Automate when a new file is added to a folder. See this for example: https://manueltgomes.com/microsoft/how-to-update-sharepoint-with-excel-files-in-a-folder/

Upvotes: 0

Related Questions