Power Automate and Excel: Finally Killing the Monthly Report Everyone Hates

The Scenario: A Monthly Operations Report That Won’t Die

You probably recognise this pattern:

• Source: A transactional system sends a CSV or XLSX file each month (sales, tickets, inventory, etc.).
• Model: An Excel workbook on SharePoint with:
  • A data tab that ingests the monthly file.
  • A set of formulas and pivot tables that calculate KPIs.
• BI Layer: A Power BI dataset that uses that Excel file as a source.
• Output: A PDF pack (or PowerPoint) with a fixed set of charts and tables.
• Distribution: Email to a list of managers, plus archive in a SharePoint folder.

The current process:

1. Download the source file from email.
2. Paste or import into the Excel model.
3. Refresh pivots, sanity‑check totals.
4. Export a PDF.
5. Manually refresh Power BI (or wait for scheduled refresh).
6. Email the PDF.

The target process:

• Monthly file arrives in a shared mailbox.
• Power Automate:
  • Saves it to SharePoint.
  • Loads it into the Excel model.
  • Triggers a Power BI dataset refresh.
  • Generates a PDF from a report template.
  • Emails the PDF and logs success/failure.

All without a human, except when something breaks.

Architecture: How Power Automate, Excel and Power BI Fit Together

For this kind of Power Automate Excel flow, keep the architecture simple and cloud‑native:

• Storage: SharePoint or OneDrive for Business.
  • Required for most Excel connector actions that work reliably in unattended flows.
• Excel file type:
  • Use .xlsx (or .xlsm if you truly need macros). The Excel Online (Business) connector works with both, but can’t execute VBA.
• Data model:
  • Prefer Excel tables and formulas over macros. Power Automate can read/write tables; it cannot run your VBA.
• Power BI dataset:
  • Hosted in the Power BI service (workspace). Refreshed via the Power BI connector.
• Report output:
  • Either a PDF exported from a Power BI report, or a PDF generated from an Office template (Word or Excel).

You’re not replacing Excel or Power BI. You’re automating:

• File arrival → ingestion → transformation → BI refresh → distribution.

Step 1 – Trigger: When the Monthly File Arrives

The most robust trigger for this scenario is usually a mailbox rule plus an Outlook trigger.

Trigger options that work well:

1. Outlook: When a new email arrives (V3)

   • Scope: Shared mailbox or a dedicated reporting mailbox.
   • Filter: Subject contains e.g. Monthly Ops Export and/or attachment name pattern.

2. SharePoint: When a file is created (properties only)

   • If the upstream system can drop the file directly in a SharePoint library.

For the email option, configure:

• Has Attachments = Yes.
• Optional Include Attachments = Yes (if you want the content directly).

Then first action:

• Condition to check the attachment name or extension (e.g. ends with .csv or .xlsx).

This prevents random emails from triggering your flow.

Step 2 – Store the Source File in SharePoint

Even if the attachment is usable as‑is, store it in a controlled location:

1. Initialize variable reportMonth (String) from the email date:
   • Use a Compose with an expression like:

formatDateTime(triggerOutputs()?['body/dateTimeReceived'], 'yyyy-MM')

2. SharePoint: Create file
   • Site Address: Your reporting site.
   • Folder Path: e.g. /Shared Documents/MonthlyOps/Raw.
   • File Name: MonthlyOps_@{outputs('Compose_reportMonth')}.csv (or .xlsx).
   • File Content: Attachments Content from the trigger.

This gives you a predictable file location and name for the rest of the flow.

Step 3 – Load Data into the Excel Model

The critical design decision: don’t try to drive Excel like a desktop app. Stick to what the Excel Online (Business) connector supports reliably:

• Reading/writing tables.
• Adding rows.
• Clearing tables.
• Getting cell values.

Option A – Excel Model Pulls Directly from the Raw File

If your Excel model uses Power Query or a table pointing at the raw file in SharePoint, you have two choices:

• Let Power BI refresh the dataset from the raw file directly (skip Excel for data ingestion).
• Or keep Excel as the model and just ensure the raw file lands in the right folder.

Power Automate cannot refresh Power Query inside Excel Online as of now. If you rely on Power Query in Excel, the refresh is either:

• User‑initiated in desktop Excel.
• Automated via another mechanism (e.g. Office Script or external scheduler running Excel desktop, which is more complex and licence‑dependent).

For a robust unattended flow, prefer Power BI pulling from the raw file instead of Excel Power Query.

Option B – Flow Writes Data into an Excel Table

A more Power Automate‑friendly pattern:

• Excel model has a table FactData where the monthly data lives.
• Formulas/pivots read from FactData.

You then:

1. Excel Online (Business): List rows present in a table (if you need to inspect or clear).
2. Excel Online (Business): Delete all rows from the table (using a loop or table clear pattern) to avoid mixing months.
3. Parse the CSV using the Data Operations: Compose / Parse JSON or a custom parser if the format is simple.
4. Excel Online (Business): Add a row into a table in a loop.

This is workable for moderate row counts. For large datasets, writing row‑by‑row from Power Automate can be slow; at that point, Power BI or Power Query is usually a better ingestion layer.

Key constraints to respect:

• The Excel file must be closed (no lock from desktop users) when the connector writes.
• The table schema (column names, types) must match what you’re writing. The connector doesn’t automatically adapt.

Step 4 – Refresh the Power BI Dataset

Once the data is in place (either in the raw file or the Excel model), trigger a dataset refresh.

Use the Power BI connector:

• Action: Refresh a dataset.
• Parameters:
  • Workspace: The workspace where the dataset lives.
  • Dataset: The dataset that reads your Excel or CSV.

The refresh is asynchronous. To avoid overlapping refreshes:

• Consider adding a delay if upstream steps might take time.
• Or use the Power BI REST API with a custom connector for more advanced control (e.g. checking refresh status), if your environment allows it.

Common gotchas:

• If the dataset uses an Import mode with SharePoint/OneDrive as source, the refresh runs in the service; Power Automate just triggers it.
• If the dataset uses DirectQuery, triggering a refresh behaves differently (it refreshes some metadata/caches rather than re‑importing data). For a monthly batch report, Import is usually the right mode.

Step 5 – Generate the Monthly Report PDF

You have two main patterns for generating the PDF:

Pattern 1 – Export a Power BI Report to PDF

If your monthly pack is essentially the Power BI report:

1. Action: Export To File for Power BI Reports (Power BI connector).

   • Workspace: Reporting workspace.
   • Report: The report built on the refreshed dataset.
   • Export Format: PDF.
   • Optional: Specific pages or bookmarks if you only need a subset.

2. Once export completes, use the File Content output as the attachment in email or to create a file in SharePoint.

This avoids dealing with Excel formatting entirely.

Pattern 2 – Use an Office Template (Word + Excel data)

If your output is a more narrative report, Word templates with content controls work well:

1. Design a Word template stored in SharePoint with:

   • Plain text content controls bound to key KPIs.
   • Repeating content controls for tables if needed.

2. In the flow:

   • Use Excel actions to get key cell values (e.g. total revenue, number of tickets).
   • Use the Word Online (Business) connector:
     • Populate a Word document with those values.
   • Then Convert Word document to PDF.

This keeps Excel as a calculation engine, but uses Word for layout.

Power Automate does not currently support running VBA to export Excel directly to PDF in the cloud. Any Excel‑centric PDF export that depends on macros will require a desktop‑side automation approach.

Step 6 – Email and Archive the Report

Finally, send the report and keep a record.

1. Outlook: Send an email (V2)

   • To: Distribution list or multiple addresses.
   • Subject: Monthly Ops Report - @{outputs('Compose_reportMonth')}.
   • Body: Short summary plus a link to the SharePoint location.
   • Attachments:
     • Name: MonthlyOpsReport_@{outputs('Compose_reportMonth')}.pdf.
     • Content: From the export/convert step.

2. SharePoint: Create file in an archive folder:

   • Save the same PDF content with the month in the filename.

3. Log the run:

   • Use SharePoint: Create item in a list or Dataverse to record:
     • Report month.
     • Run time.
     • Success/failure.
     • Key metrics (optional).

This gives you traceability when someone asks, “Did the report actually go out last month?”

Error Handling and Guard Rails (So You Can Sleep at Month‑End)

For a monthly report, robust error handling matters more than shaving seconds off execution.

Key patterns to implement:

1. Scoped try/catch using scopes

   • Wrap critical steps (file save, data write, dataset refresh, export) in separate Scope actions.
   • Configure a parallel Scope for error handling using Configure run after set to has failed or has timed out.

2. Notify on failure

   • In the error scope, send a targeted email to the report owner including:
     • Flow run ID.
     • Step that failed.
     • Error message (from the action's outputs where available).

3. Validate inputs early

   • Check attachment type and basic schema (e.g. header row) before writing to Excel.
   • If validation fails, log and notify, don’t try to process.

4. Concurrency control

   • For a scheduled monthly flow, set Concurrency Control on the trigger (where supported) to 1 to avoid overlapping runs.
   • For email‑driven flows, consider ignoring messages received within a short window if duplicates are possible.

Before/After: What Actually Changes for the Team

Using the scenario:

Before

• Analysts:
  • Spend hours on copy/paste and manual checks.
  • Are the bottleneck at month‑end.
• Managers:
  • Get the report at slightly different times each month.
  • Worry about version drift between Excel and Power BI.

After

• The flow:
  • Triggers when the source file arrives.
  • Writes data to a stable location.
  • Refreshes the Power BI dataset.
  • Exports a PDF and emails it.
  • Logs the run.
• Analysts:
  • Focus on anomalies and interpretation instead of mechanics.
  • Only step in when the flow flags a failure.
• Managers:
  • Get the report consistently.
  • See the same numbers in Excel‑based PDFs and Power BI.

You haven’t removed Excel or Power BI. You’ve removed the manual glue.

One Practical Takeaway

When you automate a hated monthly Excel report, design the flow so that Excel becomes a stable data and calculation asset, not a desktop script you try to remote‑control. Tables, cloud storage, and Power BI exports are what Power Automate handles well; if you align your model to those, you get reliability without losing the nuance of the report.