Python vs Power Automate for Report Workflows

In a Microsoft 365 organisation, the question comes up as soon as someone automates a report: build it as a Power Automate flow, which IT already supports and a business user can edit, or as a Python script, which a developer or analyst maintains? Both can watch a folder, read a workbook, build something and email it. They differ in what they are good at, what they cost to run, and who can fix them when they break. This page compares them for recurring report workflows and shows where each fits. It is part of Choosing a Python Excel Library.

The workflow being compared

A typical weekly report: every Monday, three regional managers have dropped a sales export into a shared folder. Combine the files, total revenue by region and product, flag products whose units fell by more than 20% on the prior week, write a formatted workbook, and email it to a distribution list.

How each tool handles each step

Triggers. Power Automate's strength. A cloud flow can start on a schedule, when a file is created in SharePoint or OneDrive, when an email arrives, or from a button, with no server to run. A Python script needs something to start it: cron, Windows Task Scheduler, a cloud scheduler or a job runner, and a machine or container to run on. Choose a Scheduler for Python Excel Reports covers the options.

Reading Excel data. Power Automate's Excel Online (Business) connector works with data in Excel tables, not arbitrary ranges, and "List rows present in a table" returns a limited number of rows per call by default; larger tables need its pagination setting, with a threshold that depends on the licence. Each row comes back as JSON, with dates as serial numbers unless you ask for ISO format. Python reads any range, any sheet, thousands of rows per second, with types.

Processing. This is the step that decides most cases. A flow expresses logic as actions: loops, conditions, "Filter array", "Select", expressions like add() and formatDateTime(). Combining three files and grouping by two columns means nested loops over rows, which is slow and hard to read. The same step in pandas is a few lines:

from pathlib import Path
import pandas as pd

files = sorted(Path("inbox").glob("sales_*_2026-09-27.xlsx"))
sales = pd.concat([pd.read_excel(f) for f in files], ignore_index=True)
summary = sales.pivot_table(index="Product", columns="Region", values="Revenue", aggfunc="sum", margins=True, margins_name="Total")
print(f"{len(files)} files, {len(sales)} rows")
print(summary.round(0).to_string())

The flag for products down more than 20% on the prior week is one more merge against last week's summary, two or three lines of pandas. In a flow it would be another loop over rows with a lookup inside, and another place for an off-by-one in an expression to hide.

Writing the workbook. A flow can create a file from a template and add rows to a table, one action per row, and run an Office Script for formatting. Python writes the whole formatted workbook in one pass with openpyxl or xlsxwriter, including charts and conditional formats.

Delivery. Even. The Office 365 Outlook connector sends mail as the flow's owner with a couple of fields; Python needs SMTP or Graph credentials and a few more lines. Building the message itself is simple either way:

from email.message import EmailMessage

with pd.ExcelWriter("weekly_sales_2026-09-27.xlsx", engine="xlsxwriter") as xw:
    summary.round(2).to_excel(xw, sheet_name="Summary")
    xw.sheets["Summary"].set_column(1, 4, 12, xw.book.add_format({"num_format": "#,##0"}))

msg = EmailMessage()
msg["Subject"] = "Weekly sales, week ending 27 September 2026"
msg["From"] = "reports@example.com"
msg["To"] = "sales-leads@example.com"
msg.set_content(f"Total revenue: {summary.loc['Total', 'Total']:,.0f}. Workbook attached.")
msg.add_attachment(Path("weekly_sales_2026-09-27.xlsx").read_bytes(), maintype="application",
                   subtype="vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                   filename="weekly_sales_2026-09-27.xlsx")
print(msg["Subject"], "|", len(msg.as_bytes()), "bytes")

Emailing Excel Reports with smtplib covers the sending, including through Microsoft Graph so the mail comes from a Microsoft 365 mailbox like a flow's would.

Reliability, testing and change

A flow keeps a run history with inputs and outputs for each action, which makes the first diagnosis easy. It has configurable retry policies and "run after" settings to handle failures. What it lacks is the engineering around code: unit tests, code review, diffs between versions, and a way to run yesterday's inputs through today's logic on a laptop. Flows can be exported as solution packages and put under source control, but the diff is a large JSON document rather than readable logic.

Python has all of that by default, and the burden that comes with it: dependencies to pin, a runtime to patch, logs to keep. Detect a Scheduled Report That Did Not Run covers the monitoring a script needs and a flow mostly provides.

Cost and ownership

Power Automate is included with many Microsoft 365 plans for standard connectors such as SharePoint, OneDrive, Outlook and Excel Online. Premium connectors, including the generic HTTP action and many databases, need a premium licence per user or per flow. Check your tenant's current plans rather than relying on any list, since Microsoft changes them.

Python itself costs nothing, but it runs somewhere, and someone has to own that machine or cloud service. The larger cost is usually people: a flow can be adjusted by the analyst who owns the report; a script needs someone who writes Python, and when that person leaves, an undocumented script is harder to hand over than a visible flow.

Using both

The designs that work best often combine them, each doing what it is good at:

• A flow triggers on the new file or the schedule, and calls the Python job, for example an Azure Function, as in Run an Excel Report in Azure Functions. Calling it requires the premium HTTP action.
• The Python job does the reading, processing and writing, and saves the workbook to SharePoint, as in Upload an Excel Report to SharePoint with Python.
• A second flow, triggered when the file appears, emails it and posts to Teams, which business users can reconfigure without touching the code.

Which to use

• Power Automate alone when the data is small and already in Excel tables in Microsoft 365, the logic is simple (copy, filter, notify), and business users need to own and change the workflow.
• Python alone when the processing is substantial, the inputs are files or databases outside Microsoft 365, the output needs rich formatting, or the workflow needs tests and code review.
• Both when the organisation standardises on flows for triggers, approvals and delivery, but the report itself needs real data processing.

Common pitfalls

• Row loops in a flow. "Apply to each" over thousands of rows is slow and burns through action limits; do bulk work in Python or an Office Script.
• Data outside tables. The Excel Online connector reads tables; convert ranges to tables first or read the file with Python.
• Default row limits. Enable pagination on "List rows present in a table", or large tables are silently truncated.
• Scripts nobody else can run. Document how to run the Python job, pin its dependencies and keep it in version control.
• Flows owned by one person. A flow tied to a user's account stops when the account is disabled; use a service account or shared ownership.

Conclusion

Power Automate excels at triggers, approvals and delivery inside Microsoft 365, and lets business users own simple workflows; it struggles with row-level processing, large tables and anything that needs testing. Python excels at reading, transforming and writing Excel data, with the full toolkit of software engineering, but needs a place to run and a developer to maintain it. For report workflows of any size, let a flow trigger and deliver and let Python do the processing in between.

Frequently asked questions

Can Power Automate replace a Python Excel script? For simple workflows on small Excel tables, yes. For combining files, grouping, joining or complex formatting, a flow becomes slow and hard to maintain, and Python is the better tool for that step.

Can Power Automate run a Python script? Not directly in a cloud flow. A flow can call a Python job hosted as a web endpoint or function through the premium HTTP action, and a desktop flow can run a script on a Windows machine.

Why does my flow only return some rows from an Excel table? The List rows present in a table action returns a limited number of rows by default. Turn on pagination in the action's settings and set a threshold, within the limit your licence allows.

Is Power Automate free? Standard connectors are included with many Microsoft 365 plans; premium connectors such as HTTP require a paid licence. Check your organisation's plan, as the details change.

Related

• Up one level: Choosing a Python Excel Library — picking the right tool for each job.
• Python vs VBA for Excel Automation — including Office Scripts.
• Python vs Power Query for Cleaning Excel Data — the other Microsoft low-code tool.
• Choose a Scheduler for Python Excel Reports — where the Python side runs.
• Automating Excel Online with Microsoft Graph — the API both approaches use.