Picture this: every Monday morning, your operations team drops a fresh Excel file into a shared OneDrive folder — a weekly sales report exported from your CRM, containing hundreds of rows of raw transaction data. Someone on your team then opens it, applies some filters, copies the clean data into a master tracker, and emails a summary to the regional managers. That person is spending two hours every week on work a well-built Power Automate flow could handle in under two minutes.
This isn't a niche scenario. Excel and OneDrive sit at the center of how most mid-sized organizations actually move data around. Power BI gets the glamour, but Excel is still the workhorse. The problem isn't the tool — it's the manual labor required to keep it running. Power Automate's Excel Online (Business) connector gives you the ability to read table data, add and update rows, and trigger flows based on file changes without ever touching the spreadsheet yourself.
By the end of this lesson, you'll have built a complete, production-ready automation that monitors a OneDrive folder for new Excel files, reads their contents, applies transformations and business logic, writes processed results to a master workbook, and sends a formatted summary notification. You'll understand not just the mechanics of each step, but why the connector works the way it does — including its limitations, which will save you hours of debugging.
What you'll learn:
You should be comfortable with the basics of Power Automate: building flows with triggers and actions, working with dynamic content, and using the expression editor for simple operations. If you've built at least two or three flows before, you're ready for this. You'll also need a Microsoft 365 account with OneDrive for Business, and Excel files stored there (not SharePoint Libraries are also supported, but we'll use OneDrive for this lesson). Familiarity with Excel tables (as opposed to plain ranges) is assumed.
Before you write a single action, you need to understand something that trips up nearly every practitioner new to Excel automation in Power Automate: the connector only works with named Excel Tables, not with plain worksheet ranges.
This is a hard constraint, not a preference. When Power Automate reads from Excel, it calls the Excel Online API which requires a structured table object with defined column headers. A range of cells — even one that looks like a table to a human — is invisible to the connector. If you point an action at a worksheet that has no formal table, you'll get an error like "The table was not found" or the table dropdown will simply be empty.
Here's how to verify and fix this before building your flow. Open your Excel file, click anywhere inside your data range, then go to the Insert tab and choose Table. Make sure "My table has headers" is checked. Excel will format the range as a table and assign it a default name like Table1. Rename it something meaningful — SalesData, WeeklyTransactions, or whatever reflects its content — by clicking on the table, going to the Table Design tab, and changing the name in the Table Name field on the left.
Why does naming matter? Because when Power Automate lists available tables in the action dropdown, it shows the table name. A flow referencing Table1 will break the moment someone deletes and re-creates that table, because the new one will get a different auto-generated name. A table named WeeklyTransactions is stable, self-documenting, and much easier to reference in expressions.
Warning: The table name is case-sensitive in the API calls Power Automate makes under the hood. If you rename a table mid-build, your existing actions may not update automatically. Delete and re-add the action to pick up the new name.
Your Excel files for this lesson should have this structure:
WeeklyReport.xlsx (the incoming file, dropped by the operations team):
| TransactionID | Region | SalesRep | Product | Amount | Status |
|---------------|-----------|---------------|----------------|---------|-----------|
| TXN-1001 | Northeast | Sarah Chen | Enterprise Pro | 8400.00 | Closed Won|
| TXN-1002 | Southeast | Marcus Webb | Starter Pack | 1200.00 | Pending |
| TXN-1003 | Midwest | Priya Nair | Enterprise Pro | 9100.00 | Closed Won|
Table name: WeeklyTransactions
MasterTracker.xlsx (the persistent master workbook your flow writes to):
| TransactionID | Region | SalesRep | Product | Amount | Status | ProcessedDate | WeeklyFile |
|---------------|-----------|---------------|----------------|---------|------------|---------------|-----------------|
Table name: ProcessedTransactions
Create both files, put them in OneDrive, and you're ready to build.
The trigger for this flow is "When a file is created" from the OneDrive for Business connector. This trigger fires every time a new file appears in a folder you specify — perfect for a scenario where files land on a schedule.
In Power Automate, create a new Automated Cloud Flow and search for "When a file is created (properties only)" from OneDrive for Business. The "(properties only)" variant is important: it gives you file metadata like the name, path, and ID without downloading the file content, which is more efficient when you're working with the Excel connector (which accesses the file through its path, not as a binary download).
Set the Folder field to the OneDrive folder where the operations team drops files — for example, /SalesReports/Weekly. Leave the "Include subfolders" option off unless your structure requires it.
After saving this trigger, you'll have access to dynamic content from it. The most important pieces you'll use later are:
WeeklyReport_2024-11-04.xlsx)/SalesReports/Weekly/WeeklyReport_2024-11-04.xlsx)Tip: The "When a file is created (properties only)" trigger polls on a schedule (typically every 1-3 minutes for paid plans). It is not a true real-time webhook. If you need sub-minute response times, you'll need a different architecture, but for batch file processing this is entirely sufficient.
One practical consideration: if the operations team uploads a file and then immediately uploads a corrected version, the trigger will fire twice. We'll handle deduplication logic later when we write to the master tracker.
Now you need to actually read the rows from the file that just landed. Add the action "List rows present in a table" from the Excel Online (Business) connector.
This action requires three things:
Here's where most practitioners make their first mistake: they try to use the Path dynamic content from the trigger directly in the File field. Sometimes this works, but more often the connector fails because the path format doesn't match what it expects. The more reliable approach is to use the Id from the trigger, which uniquely identifies the file regardless of path formatting.
Click the File field and switch to "Enter custom value" instead of using the folder picker. Then use this expression:
triggerOutputs()?['body/Id']
For the Table field, type WeeklyTransactions directly rather than relying on the dropdown, which requires the connector to enumerate tables from a specific file and often times out during flow design.
After configuring this action, Power Automate returns an object containing a value array — each element of that array is one row from your Excel table, represented as a JSON object with keys matching your column headers.
The raw output looks like this:
{
"value": [
{
"TransactionID": "TXN-1001",
"Region": "Northeast",
"SalesRep": "Sarah Chen",
"Product": "Enterprise Pro",
"Amount": 8400,
"Status": "Closed Won"
},
{
"TransactionID": "TXN-1002",
"Region": "Southeast",
"SalesRep": "Marcus Webb",
"Product": "Starter Pack",
"Amount": 1200,
"Status": "Pending"
}
]
}
Notice that Amount comes back as a number, not a string. The connector respects Excel's cell type, which matters when you write expressions — you won't need to convert it for math operations, but you may need to format it as currency for display purposes.
Tip: If you have a large file (thousands of rows), the "List rows present in a table" action returns up to 5,000 rows by default. If you need more, enable pagination in the action's settings (the three dots > Settings > Pagination) and set the threshold. Be aware that very large datasets significantly increase flow run time and may hit the 30-day run history data limits.
Raw data rarely goes straight from source to destination unchanged. In our scenario, we want to:
This transformation logic lives inside an Apply to each loop. Add this action and set the input to the value array from the "List rows present in a table" output:
outputs('List_rows_present_in_a_table')?['body/value']
Inside the loop, add a Condition action to filter pending transactions. Set the left side to:
items('Apply_to_each')?['Status']
Set the operator to "is not equal to" and the right side to the static text Pending.
In the Yes branch (meaning Status is not Pending), you'll do the transformation work. Add a Compose action and name it "Calculate Commission." Use this expression:
if(
equals(items('Apply_to_each')?['Product'], 'Enterprise Pro'),
mul(float(items('Apply_to_each')?['Amount']), 0.07),
mul(float(items('Apply_to_each')?['Amount']), 0.05)
)
This expression uses Power Automate's if(), equals(), mul(), and float() functions. The float() conversion is a safety measure — even though Excel returns numeric types, defensive casting prevents type mismatch errors when expressions get composed.
Add another Compose action for the processed date:
formatDateTime(utcNow(), 'yyyy-MM-dd')
And one more for the source file name, pulling from the trigger:
triggerOutputs()?['body/Name']
Warning: Don't use
utcNow()inside a loop expecting it to produce different timestamps per iteration — it evaluates once when the action runs, but within a tight loop the values will all be identical anyway. This is fine for a "processed date" field. It's not fine if you need precise per-row timestamps for audit logs; for that, use the flow's run timestamp or generate IDs differently.
At this point, your loop produces transformed values for each qualifying row. The next step is writing those values to the master tracker.
Inside the same Yes branch of your condition (after the Compose actions), add the action "Add a row into a table" from the Excel Online (Business) connector.
Configure it to point to your MasterTracker.xlsx file and the ProcessedTransactions table. The columns will appear as fields once the connector loads the table schema. Map them like this:
| Column | Value |
|---|---|
| TransactionID | items('Apply_to_each')?['TransactionID'] |
| Region | items('Apply_to_each')?['Region'] |
| SalesRep | items('Apply_to_each')?['SalesRep'] |
| Product | items('Apply_to_each')?['Product'] |
| Amount | items('Apply_to_each')?['Amount'] |
| Status | items('Apply_to_each')?['Status'] |
| Commission | outputs('Calculate_Commission') |
| ProcessedDate | outputs('Processed_Date') |
| WeeklyFile | outputs('Source_File_Name') |
One important note on the Commission field: the master tracker's Commission column should be formatted as a number in Excel, not currency. The connector writes the raw numeric value; Excel's number formatting handles how it displays. If you write 588.0 (7% of $8,400), Excel can display it as $588.00 based on the cell format — you don't need the flow to format it.
Here's a production concern the happy-path tutorials don't cover: what happens if the same file gets uploaded twice, or if the flow is re-run after a failure? You'll end up with duplicate rows in your master tracker.
The most robust solution is to check for existing records before adding. Add a "List rows present in a table" action (pointed at MasterTracker.xlsx) before the "Add a row" action, with a filter query:
In the Filter Query field, use:
TransactionID eq 'TXN-1001'
But since the TransactionID is dynamic, use:
TransactionID eq '@{items('Apply_to_each')?['TransactionID']}'
Tip: The filter query syntax here is OData-style filtering, which the Excel connector supports. For text fields, wrap the value in single quotes. For numeric fields, omit the quotes. Supported operators include
eq,ne,gt,lt,ge,le,and,or.
After this list action, add a condition: "If the value array from this list action has a length of 0, add the row; otherwise, skip it."
The expression to check:
length(outputs('Check_for_existing_row')?['body/value'])
If this equals 0, proceed with adding. If not, log a message or update the existing row instead.
Sometimes you don't want to skip duplicates — you want to update the existing row with newer data. The "Update a row" action handles this, but it requires the row's key column value, not just any column.
The Excel connector uses the first column of the table as the implicit key for updates. In our ProcessedTransactions table, that's TransactionID. Use the "Get a row" action first to retrieve the full row object, then feed that into "Update a row."
The "Get a row" action takes a key value:
items('Apply_to_each')?['TransactionID']
It returns the full row including Power Automate's internal @odata.etag and row identifier. The "Update a row" action then takes the key value and the columns you want to change:
Key Column: TransactionID
Key Value: items('Apply_to_each')?['TransactionID']
Status: items('Apply_to_each')?['Status']
Amount: items('Apply_to_each')?['Amount']
Warning: "Update a row" performs a partial update — only the fields you specify change. But if you use "Add a row" on an existing key value, you'll create a duplicate, not an upsert. The Excel connector does not have a native upsert operation. Your check-then-act logic is the only way to get upsert behavior.
After the loop finishes, you want to send a summary email showing how many transactions were processed, what the total sales amount was, and how many were filtered out.
This requires accumulator variables initialized before the loop. Before your Apply to each action, add three Initialize variable actions:
TotalProcessed, Type: Integer, Value: 0TotalAmount, Type: Float, Value: 0SkippedCount, Type: Integer, Value: 0Inside the loop, in the Yes branch (after adding the row), add an Increment variable action for TotalProcessed by 1, and another for TotalAmount by:
float(items('Apply_to_each')?['Amount'])
In the No branch of your condition (Pending transactions), increment SkippedCount by 1.
After the Apply to each loop closes, add a Send an email (V2) action from the Office 365 Outlook connector. Format the body like this:
Weekly Sales File Processed Successfully
File: @{triggerOutputs()?['body/Name']}
Processed: @{variables('TotalProcessed')} transactions
Total Amount: $@{formatNumber(variables('TotalAmount'), 'N2')}
Skipped (Pending): @{variables('SkippedCount')} transactions
Processed On: @{formatDateTime(utcNow(), 'MMMM dd, yyyy')}
The formatNumber() function formats the float as a number with two decimal places (N2 is the standard .NET numeric format specifier). This gives you $48,700.00 instead of 48700.000001 (floating point artifacts are real and formatNumber handles them gracefully).
A flow that works on clean data in a dev environment is not a production flow. Let's add the error handling infrastructure that makes this reliable.
By default, Power Automate only runs each action when the previous one succeeds. But in a loop, a single failed row write will abort the entire flow unless you configure otherwise.
On your "Add a row into a table" action, click the three dots and select "Configure run after." Check "has failed" in addition to "is successful." Then add a parallel branch with an Append to string variable action (initialize ErrorLog as a string variable before the loop) that captures error details:
Transaction @{items('Apply_to_each')?['TransactionID']} failed to write. Error: @{actions('Add_a_row_into_a_table')?['error/message']}
This way the loop continues even when individual rows fail, and you get a collected error log you can include in the notification email.
For more complex flows, wrap major sections in Scope actions. A Scope is a container that groups multiple actions, and you can configure subsequent actions to run only if the Scope fails. This gives you try/catch behavior:
The failure notification should include:
outputs('Process_File')?['error/message']
This gives stakeholders immediate visibility when something goes wrong, rather than silent failures that go unnoticed until someone checks the tracker manually.
Not every file that lands in the folder will be valid. A common real-world issue is the operations team uploading a CSV accidentally, or uploading an Excel file that doesn't have the expected table structure.
Add a Condition near the top of your flow that checks the file extension:
endsWith(triggerOutputs()?['body/Name'], '.xlsx')
If false, send an alert email and terminate the flow using the Terminate action with a status of "Cancelled" and a descriptive message.
Now that you understand each component, build the entire flow from scratch using this specification. This is the full production version.
Objective: Process incoming weekly sales files, write qualified transactions to a master tracker, and send a formatted summary to regional managers.
Step 1 — Prepare your Excel files. Create WeeklyReport.xlsx with the WeeklyTransactions table (6 columns as defined earlier). Populate it with at least 10 rows, mixing "Closed Won" and "Pending" statuses, and both product types. Create MasterTracker.xlsx with the ProcessedTransactions table (9 columns). Upload both to OneDrive.
Step 2 — Create the flow. Trigger: "When a file is created (properties only)" in OneDrive for Business, pointing to your weekly reports folder.
Step 3 — Add validation. Check that the file ends with .xlsx. Terminate if not.
Step 4 — Initialize variables. TotalProcessed (Integer, 0), TotalAmount (Float, 0), SkippedCount (Integer, 0), ErrorLog (String, empty).
Step 5 — Read the source file. "List rows present in a table" using the trigger's file Id, table name WeeklyTransactions.
Step 6 — Loop and transform. Apply to each on the value array. Condition on Status not equal to "Pending." In the Yes branch: Compose Commission (if/else formula), Compose ProcessedDate, Compose SourceFileName. Check for duplicate in master tracker. If not duplicate, add row. If duplicate, update row. Increment TotalProcessed and TotalAmount. In the No branch: increment SkippedCount.
Step 7 — Send summary. After the loop, send email with TotalProcessed, TotalAmount (formatted), SkippedCount, and any ErrorLog content.
Step 8 — Test. Upload the test WeeklyReport.xlsx file to your watched folder and monitor the flow run. Check MasterTracker.xlsx to verify rows were added correctly. Upload the same file again and verify that duplicate protection works.
Validation criteria:
Cause: The Excel file doesn't have a formal table (it's a plain range), the table name doesn't match what's in the flow, or the file path is wrong.
Fix: Open the file in Excel, confirm the table exists under the Table Design tab, verify the exact name (case-sensitive), and re-configure the action by deleting and re-adding it so the dropdown reloads.
Cause: The "List rows present in a table" action returned an empty result. This usually means the table exists but is empty, or more commonly, the flow is looking at the wrong file.
Fix: Add a Compose action right after "List rows present in a table" that outputs outputs('List_rows_present_in_a_table')?['body/value']. Check the run history — if it's an empty array, the table is empty or the wrong file is being read.
Cause: Floating point arithmetic. Multiplying floating point numbers in any language produces these artifacts.
Fix: Wrap your multiplication expression in a round() call: round(mul(float(...), 0.07), 2). This rounds to two decimal places.
Cause: Large files being uploaded to OneDrive may not be fully written before the trigger fires. The Excel connector then tries to read a partially uploaded file.
Fix: Add a Delay action (30 to 60 seconds) immediately after the trigger, before any Excel actions. This gives OneDrive time to complete syncing. This is a pragmatic workaround, not an elegant solution — but it works reliably in practice.
Cause: One of the values you're writing doesn't match the column's expected type. Most commonly, a number column is receiving a string, or a date column is receiving an unrecognized format.
Fix: Check each value being written. Wrap numeric values in float() or int(). For date columns, format with formatDateTime(utcNow(), 'yyyy-MM-dd').
Cause: Special characters in the TransactionID (like hyphens or slashes) can break the OData filter string. Also, single quotes inside a filter value must be escaped as two single quotes.
Fix: Test your filter expression in isolation first. For IDs with hyphens (TXN-1001), hyphens are safe. For values that might contain apostrophes (like a person's name), replace single quotes: replace(items('Apply_to_each')?['SalesRep'], '''', '''''').
Cause: "Apply to each" loops run sequentially by default. A 500-row file with a duplicate check query per row means 500 individual API calls to Excel, which can take 15-20 minutes.
Fix: In the Apply to each action's settings, enable "Concurrency Control" and set the degree of parallelism to between 5 and 10. Be careful: parallel loops interact unpredictably with sequential operations (like incrementing variables) — use the Increment variable action which is atomic, not Compose+Set variable which is not.
You've built a complete, production-grade Excel automation in Power Automate. Let's review what makes it genuinely production-ready as opposed to a demo:
Where to go from here:
The natural next step is adding SharePoint Lists as an alternative output — in many organizations, the master tracker is better served as a SharePoint List rather than an Excel file, because it supports concurrent access, versioning, and direct integration with Power Apps. The logic you've built here transfers directly; only the "Add a row" action changes.
You should also explore Power Automate's "Get file content" action combined with a Parse JSON action if you need to process Excel-like data that isn't formatted as a table — this is common when dealing with files exported from legacy systems.
For more complex transformations that exceed what expressions can cleanly handle, look at invoking an Azure Function from Power Automate. You can pass the Excel data as a JSON payload, do the heavy lifting in Python or C#, and return the transformed result. This is the right architecture when your business logic involves lookups against external databases, complex statistical calculations, or multi-source merges.
Finally, if you find yourself building variations of this flow for different file types or departments, Power Automate's solution-based architecture lets you package flows with their connections and deploy them across environments — turning this one-off automation into a reusable template your whole organization can benefit from.
Learning Path: Flow Automation Basics