How to merge Excel files with Power Automate
By the Analistable team · Updated · 3 min read
In Power Automate, create a Scheduled cloud flow, add List files in folder (OneDrive for Business or SharePoint), and for each file use List rows present in a table followed by Add a row into a table in a master workbook. Every source must store its data as an Excel table with the same name. Turn on Pagination so files with more than 256 rows are read completely.
Part of our guide: How to merge Excel files: every method compared
What you need
- A Microsoft 365 work or school account with Power Automate.
- Source files in one OneDrive for Business or SharePoint folder.
- In every source file, the data formatted as a table (Ctrl+T) with the same table name, for example
Sales. - A master workbook with an empty table that has the same columns.
Build the flow
- Create a Scheduled cloud flow and set how often it runs (for example daily at 06:00).
- Add List files in folder (OneDrive for Business) and choose the source folder.
- Add Apply to each over the value list from that step.
- Inside it, add List rows present in a table. For File, use the dynamic Id of the current file; type the table name (
Sales) as a custom value. - In that action's Settings, turn on Pagination and set a threshold larger than your biggest file. Without it, only the first 256 rows are returned.
- Add another Apply to each over the rows, and inside it Add a row into a table pointing at the master workbook and table. Map each column.
- Save and run a test.
Clear the master table first (or write to a fresh copy each run), otherwise every run appends the same rows again.
The speed problem, and the fix
Adding rows one at a time means one connector call per row. A few hundred rows are fine; tens of thousands take a long time and can hit service limits. For large merges, replace the row loop with two Run script actions using Office Scripts: one script returns a file's rows as an array, the other writes the whole array to the master sheet in one call. The scripts are in combining workbooks with Office Scripts.
Common errors
| Message or symptom | Likely cause |
|---|---|
| “No table was found with the name…” | A source file has no table, or it's named differently |
| Only 256 rows copied | Pagination is off on List rows present in a table |
| Rows duplicated each run | The master table isn't cleared between runs |
| File locked errors | Someone has the file open for editing; retry or schedule out of hours |
Is Power Automate the right tool?
It's the right choice when the merge must run without anyone opening Excel. If someone opens the report anyway, Power Query with refresh on open is simpler and much faster. For one-off merges, the Analistable combiner needs no setup.
Frequently asked questions
- Can Power Automate merge Excel files?
- Yes. A flow can list files in a OneDrive or SharePoint folder, read each file's table and add the rows to a master table, on a schedule or when a new file arrives.
- Why does Power Automate only read 256 rows?
- List rows present in a table returns 256 rows by default. Turn on Pagination in the action's settings and set a higher threshold.
- Does the data have to be in an Excel table?
- Yes, the standard Excel connector reads and writes tables only. Office Scripts can read plain ranges if you use the Run script action.