0

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

Improve this question
3
  • I would use a Power Query to do the combining. Use Power Automate to trigger the refresh. In the meantime you might like to edit your post to actually include a question Commented Sep 4, 2024 at 13:21
  • Office =Scripts will do it for you, or just use the built in operations for Excel IF you're using tables. Commented Sep 8, 2024 at 11:39
  • Thank you @CHill60 and Skin for your reply. I know that it could accomplish within PowerBI or using scripts but in my case the name of Excel files pushed to the Sharepoint folder change over time. Indeed, I have for example in my Sharepoint folder 5 Excel files and I can merged all together in one master file (new Excel file). How can I add additional rows from new incoming Excel files in the Sharepoint folder to my master file. In Power Automate you can trigger an action respecting a modification or when a new file is added to the Sharepoint folder but I don't know how to implement it. Commented Sep 9, 2024 at 7:34

1 Answer 1

Reset to default
0

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.