A few months ago, one of our clients — a mid-size operations team — was spending four hours every Friday manually pulling data from a SharePoint list, cross-referencing it against an Excel workbook, and assembling a CSV report to send to their finance department. Four hours. Every single week. When they came to us at Helion 360, my first thought was: Power Automate can fix this in an afternoon.
Spoiler: it took slightly longer than an afternoon. But the end result was a fully automated pipeline that runs every Friday at 7 AM, produces a clean CSV, and lands in the right inbox before anyone's poured their first coffee. Here's exactly how I built it.
Understanding the Data Architecture First
Before touching Power Automate, I spent time understanding what data lived where and why. This step is non-negotiable. The client had:
- A SharePoint list acting as the master record for active projects — including project ID, client name, and budget codes.
- An Excel workbook stored in SharePoint (OneDrive for Business, same tenant) containing weekly expense submissions tied to those project IDs.
- A requirement to join these two datasets on project ID and export matched rows as a CSV for finance.
The lookup logic was straightforward on paper: for each expense row in Excel, find the matching project in the SharePoint list, pull the budget code and client name, and append them to the output. In practice, doing this at scale inside Power Automate requires deliberate structure.
Setting Up the Flow: The Core Components
Step 1 — Trigger and Initialize Variables
I used a Scheduled cloud flow trigger set to every Friday at 7:00 AM. Then I initialized three variables at the top of the flow:
varCSVRows— an array to collect each output row as a string.varHeader— a string containing the CSV column headers.varProjectCache— an object variable I'd use to store SharePoint lookup results and avoid redundant API calls.
That last variable is the performance trick most tutorials skip. If you're looking up the same project ID fifteen times across fifteen expense rows, you don't want fifteen separate SharePoint API calls. Cache the results on first lookup, reuse them after.
Step 2 — Load SharePoint List Data into a Cache Object
I used the Get items action to retrieve all rows from the SharePoint Projects list (with a filter to only pull active projects). Then, inside an Apply to each loop, I built a key-value object where each project ID maps to its budget code and client name. I stored this in varProjectCache using the Set variable action with a JSON merge expression.
This pre-loading pattern means the Excel lookup phase runs entirely from memory rather than making live SharePoint calls per row — a significant speed improvement when working with hundreds of expense records.
Step 3 — Read the Excel Workbook Row by Row
Using the List rows present in a table action (Excel Online — Business connector), I pulled all rows from the named table in the workbook. Power Automate requires your Excel data to be in a formatted Table object, not just a range — if your source file isn't set up this way, fix that first or the action will fail silently in confusing ways.
Inside the loop over Excel rows, I extracted the project ID from each row, then used a Parse JSON action combined with a variables('varProjectCache') expression to retrieve the cached SharePoint data for that ID.
Step 4 — Build Each CSV Row as a String
This is where people often reach for third-party connectors or premium actions unnecessarily. CSV is just comma-separated text. I used a Compose action to assemble each row using a concat expression:
concat(item()?['ExpenseDate'], ',', item()?['Amount'], ',', item()?['ProjectID'], ',', variables('varProjectCache')?[item()?['ProjectID']]?['ClientName'], ',', variables('varProjectCache')?[item()?['ProjectID']]?['BudgetCode'])
I then appended each composed row to varCSVRows using the Append to array variable action.
Step 5 — Join the Array and Create the CSV File
Once all rows are processed, I used a Compose action to join the header row and the data rows:
concat(variables('varHeader'), decodeUriComponent('%0A'), join(variables('varCSVRows'), decodeUriComponent('%0A')))
The decodeUriComponent('%0A') trick gives you a real newline character inside an expression — one of those little Power Automate gotchas that costs an hour if you don't know it.
I then used Create file (SharePoint connector) to write the output as a .csv file to a designated Reports folder, with a dynamic filename including the current date.
Step 6 — Send the Report via Email
Finally, a Send an email (V2) action (Office 365 Outlook connector) fires with the CSV attached using Get file content from the just-created SharePoint file. The finance team receives it with a summary in the email body and the full data in the attachment.
Handling Errors Gracefully
Production flows need error handling. I wrapped the main processing scope in a Scope action and added a parallel branch configured to run only on failure. That branch sends an alert email to the ops team with the failed run details pulled from the result() expression. It's saved three support tickets already.
Results and What I'd Do Differently
The flow processes around 300 rows in under two minutes. The Friday morning manual process is completely eliminated. The client estimates roughly 200 hours saved annually — not counting the errors that no longer happen.
If I were rebuilding this today, I'd consider using Dataverse for the project master data instead of SharePoint lists, purely for the richer querying options. I'd also explore whether the Excel file could be replaced with a SharePoint list itself, which would make the join logic even cleaner and eliminate the Excel connector's occasional quirks with large tables.
But for most teams already living in SharePoint and Excel? This pattern works. It's reliable, uses standard connectors, and doesn't require premium licensing beyond what most Microsoft 365 Business plans already include.
Key Takeaways
- Pre-cache your lookup data — don't call SharePoint inside every Excel row loop.
- Ensure Excel data is in a named Table — not just a range.
- Build CSV manually with concat and join — no premium connectors needed.
- Use decodeUriComponent('%0A') for newlines in expression-built CSV strings.
- Always add error-handling scope with a notification branch for production flows.
If your team is running a similar manual process every week, the Power Automate infrastructure to replace it probably already exists inside your Microsoft 365 subscription. It just needs to be wired together correctly.