How to merge Excel files with Power Automate
By the Analistable team · Updated · 5 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.
Starting a fresh master each run
The standard Excel connector has no single “clear table” action, and deleting rows one at a time is as slow as adding them. Two practical patterns:
- Copy a template. Keep an empty master workbook called
Master template.xlsxwith the table already set up. At the start of the flow, use the OneDrive or SharePoint Copy file action to copy it to a dated name such asCombined 2026-10.xlsx, then write rows into the copy. You also keep a history of every run. - Clear with a script. Add a Run script action at the start that deletes the table's data rows. This keeps one master file whose links and pivot tables stay intact. See combining workbooks with Office Scripts for a reset script.
Adding the file name and handling dates
Add a Source column to the master table and map it to the current file's Name from List files in folder. Without it, you can't trace a wrong row back to its file.
Dates read with List rows present in a table can come through as Excel serial numbers (46296 rather than 2026-10-01) depending on the action's date format option. Check the advanced parameters of the action for a DateTime format setting and choose ISO 8601, or convert later in Excel by formatting the column as a date. Test with one file and inspect the run history before merging everything.
Running when a new file arrives instead of on a schedule
To add each file as soon as it's saved, replace the schedule with a trigger such as When a file is created for the folder (OneDrive for Business and SharePoint both offer file-created triggers). The flow then processes only the new file, so there's no need to clear the master or loop over the folder. The catch is that a file saved before its table exists, or edited after upload, may be read too early; add a short Delay or require uploads to be finished files.
Concurrency and row order
Apply to each can process items in parallel if concurrency control is switched on in its settings. That speeds things up but means rows from different files can arrive in the master interleaved, and parallel writes to one table can collide. For merges into one table, leave concurrency off (the default) or sort the master table afterwards by Source and a date column.
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 |
| Dates arrive as five-digit numbers | Dates returned as Excel serial numbers |
| Flow runs for hours on large files | One Add a row action per row |
| Columns land in the wrong place | Mapping done by position after a source column was inserted |
For serial dates, choose the ISO 8601 date format on the read action or format the column as a date in the master. For slow runs, switch to Office Scripts. For mapping problems, re-map each field by name in Add a row into a table and keep the source tables' columns fixed.
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.