Merge Multiple Excel Files into One Master Table on SharePoint (Reusable Power Automate Solution)

Why I built this

Teams often collect data in many Excel workbooks—monthly reports, receipts, exports from Forms/Power Apps, vendor files, etc. Manually merging those into a single master worksheet is error‑prone and time‑consuming.

This post shows how I built a reusable Power Automate (cloud flow) that scans a SharePoint folder for .xlsx files, reads all tables from each workbook, and appends the rows into one fixed table (Table1) in a master workbook. The whole thing is packaged as a Power Platform Solution with Environment Variables, so anyone can import, configure, and run it—no editing actions required.

What this solution does (at a glance)

• Looks in a Source folder on SharePoint for .xlsx files (skips lock files like ~$…xlsx).
• For each workbook, finds every defined table and reads the rows.
• Appends each row into a single, fixed table in the Master workbook (e.g., Table1).
• Adds the Source file name automatically—so you can trace where each row came from.
• Ships as a Solution (unmanaged) with Environment Variables so you can point it to any site/library/folders without changing the flow definition.

Current version (row‑by‑row add): Designed for clarity and compatibility.
Future version (bulk append via Office Scripts): For large data volumes, we’ll add a variant that inserts all rows in one call (faster, fewer locks).

Who is this for?

• Analysts and engineers who receive lots of .xlsx files and want a single source of truth.
• Power users who want a drop‑in solution they can import and configure in minutes.
• IT/ops teams seeking a repeatable pattern: solution + EVs + connection references.

Overall architecture 

• SharePoint Source: /Shared Documents/SourceFiles/… (or your folder)
• Flow: “Merge Excel Files Data Into Master” (inside the Solution)
• SharePoint Destination: Master workbook (e.g., /Shared Documents/MasterFiles/Master.xlsx) with Table1
• Environment Variables: URLs, library names, folder identifiers, master path, and table name

Columns & Mapping (clear and explicit)

This flow reads all tables in each source workbook and writes into one fixed target table in the master workbook.

• Source tables (required columns): Date, Merchant, Amount, Category
• Master table (columns = 4 + 1): Date, Merchant, Amount, Category, SourceFile

The flow explicitly maps the four source columns and automatically fills SourceFile with the originating file name. You do not need SourceFile in your source files.

Planned enhancement: make mapping dynamic (by header name) or support whole‑row inserts via Office Scripts for scale and flexibility.

Prerequisites

• Access to a Power Platform environment (Developer/Production).
• Permission to import unmanaged solutions.
• SharePoint site + library for Source and Destination (Master workbook).
• Each source workbook must have at least one Table (Excel → Insert → Table). Ranges without tables are ignored.

Download & Install (fast path)

1. Download the unmanaged solution ZIP from the latest GitHub Release:
   SunilP-ExcelMergerToMaster
2. Power Apps → Solutions → Import → upload ZIP → complete wizard.
3. Open the Solution → set Environment Variables (see the table below).
4. Open the flow → Turn On → Run manually for the first test.

Environment Variables (what to set)

Display name	Schema Name	What it is / Example	Notes
EV_DestinationDocLib	sp_EV_DestinationDocLib	b!qInsudMRb0SsEejTlwqoXN03YlQxo7FNvUwhwy7oH0Il-KHA-N0EQY9WrgeqxUzS	Destination Document Library Drive ID
EV_DestinationSiteUrl	sp_EV_DestinationSiteUrl	https://<tenant>.sharepoint.com/sites/site	No trailing slash. Site hosting the master workbook.
EV_MasterFilePath	sp_EV_MasterFilePath	/Shared Documents/MasterFiles/Master.xlsx	Server‑relative path to the master file.
EV_MasterTableName	sp_EV_MasterTableName	Table1	Single target table in the master workbook.
EV_SourceFolder	sp_EV_SourceFolder	%252fShared%2bDocuments%252fSourceFiles	Use internal folder identifier from Peek code (not plain path /Shared…).
EV_SrcDocLib	sp_EV_SrcDocLib	b!qInsudMRb0SsEejTlwqoXN03YlQxo7FNvUwhwy7oH0Il-KHA-N0EQY9WrgeqxUzS	Source Document Library Drive ID
EV_SrcSiteUrl	sp_EV_SrcSiteUrl	https://<tenant>.sharepoint.com/sites/site	No trailing slash. Site hosting the source files.

Tip (important): For the source folder, the SharePoint connector expects the internal folder identifier, not the human‑friendly path. Use … → Peek code on a working “List folder” action to copy the value (it usually includes ).

Click here to view the screenshots

How I built it (step‑by‑step)

You don’t need to build it yourself—just import the solution. But if you’re curious, here’s the pattern:

1. List folder (SharePoint) → points to the Source folder
   • Filters to .xlsx, skips lock files (~$…xlsx).
2. Apply to each (files)
   • Get tables (Excel Online Business) → returns all tables in the workbook.
   • If length(value) > 0 → Apply to each (tables):
     • List rows present in a table (pagination enabled).
     • Apply to each (rows) → Add a row into a table (Master Table1).
       • Map Date, Merchant, Amount, Category
       • Set SourceFile to the current file’s Name.
3. Concurrency control
   • Writer loop set to degree = 1 to avoid Excel locking.
4. Pagination
   • Enabled on “List rows” (e.g., threshold 5000).

Testing it quickly

1. Place 2–3 small .xlsx files (each with a Table) into the Source folder.
2. Run the flow manually.
3. Open the Master workbook → check Table1 for appended rows.
4. Confirm SourceFile shows the correct file names.

Troubleshooting (real‑world gotchas)

• 404 “The response is not in JSON format” on List folder
• Use the internal folder identifier (from Peek code). Plain text paths sometimes fail.
• Excel actions stuck on “loading…”
• Repair/bind the Excel Online (Business) connection reference inside the Solution.
• Missing rows
• Turn on Pagination in “List rows present in a table” and set a suitable threshold (e.g., 5000+).
• Table not found
• Ensure each source workbook has at least one Table (Insert → Table). Ranges are not read.
• Locking/conflicts
• Keep concurrency at 1 for the loop that writes to the master; avoid opening the master workbook during runs.

Performance & scalability

• Row‑by‑row approach (current): simplest to understand, good for moderate row counts.
• Bulk append via Office Scripts (planned): 1 call to append many rows; dramatically faster and more reliable at large scale (fewer locks, fewer API calls). I’ll add a “Bulk” variant to the Solution and document it when ready.

Security & governance (practical notes)

• Use Connection References inside the Solution so each environment can bind its own credentials.
• Keep site URLs, folders, and table names in Environment Variables—no hard‑coding.
• Consider a separate service account for SharePoint access in production.
• Add run history retention and basic alerting (failed run notifications).

Roadmap

• Bulk insert version using Office Scripts
• GitHub Actions to export/unpack and publish managed packages
• Dynamic column mapping or whole‑row insert by header
• Optional telemetry (row counts, duration, throttling)

Download & contribute

• Latest Release (ZIP): SunilP-ExcelMergerToMaster
• Repo: https://github.com/spashikanti/SunilP-ExcelMergerToMaster/
• Issues/Ideas: PRs welcome—especially for the bulk variant and dynamic mapping

Screenshots / Diagrams

• Click here to view all the screenshots
• Flow overview 
  
• SourceFiles  
  

  
• Final MasterFile 
  
  
  

Final thoughts

This pattern is intentionally simple, reliable, and easy to reuse. It’s already helpful for teams that need a master dataset without manual copy‑paste. If you try it, let me know how it goes—and watch the repo for the bulk insert upgrade.

Happy automating!