By Andrew Armstrong 
There is a specific category of recurring job that eats time without producing anything interesting: open the file, refresh it, copy the table, paste it into an email, send it to the same six people. Every week. Power Automate exists precisely for this, and a working flow takes about twenty minutes to build the first time.
This guide builds one end to end. A scheduled flow that reads an Excel table stored in OneDrive or SharePoint, turns it into a formatted HTML table and emails it, then covers the limits that catch people out once they start relying on it.
Quick Facts
- Power Automate is included with most Microsoft 365 business plans. Check make.powerautomate.com before assuming you need a licence.
- The Excel connector works on files in OneDrive for Business or SharePoint, not on files sitting on a local drive or a file server.
- Your data must be formatted as a Table (
Ctrl+T). The connector reads named tables, not ranges. - List rows present in a table returns 256 rows by default. Turn on pagination for anything larger.
- Flows fail silently unless you configure notifications, always add a failure path.
What you need before you start
The source workbook has to live in OneDrive for Business or a SharePoint document library, and the data inside it has to be a proper Excel Table with a name. Select the range, press Ctrl+T, then on the Table Design tab give it a sensible name such as tblIncidents. The connector lists tables by name, and Table1 tells you nothing in six months.
If the data comes from somewhere else. An export, a database, another system. Refresh it into the workbook with Power Query first. Power Automate cannot trigger a Power Query refresh in a desktop file, which is the first real limitation people meet.
Building the flow
Quick Steps
- Go to make.powerautomate.com, choose Create, then Scheduled cloud flow.
- Name it something descriptive and set the schedule, for example every Monday at 08:00.
- Add the action List rows present in a table, and point it at your file and table.
- Add a Compose or a variable to build the HTML you want in the email body.
- Add Create HTML table from the Data Operation group, using the value from the previous step.
- Add Send an email (V2), put the HTML table output into the body, and set the recipients.
- Save, then use Test to run it manually and confirm the output before trusting the schedule.
The Create HTML table action produces plain, unstyled markup. It works, and it looks like it was produced in 1998. Wrapping it in a small amount of inline CSS in a Compose step makes it presentable.
Put that in a Compose action, then in the email body use the Compose output followed by the Create HTML table output. Outlook will render it properly.
Filtering before you send
Do not pull every row and then filter inside the flow. It is slow and burns through your action allowance. The List rows present in a table action has a Filter Query field that pushes the filter back to the source.
Status eq 'Open'
Status eq 'Open' and Priority eq 'High'
CreatedDate ge '2026-11-01'The syntax is OData, which is fussy about quotes and case. Single quotes around text values, no quotes around numbers, and the column name must match the Excel header exactly including spacing.
The limits worth knowing before you rely on it
| Limit | Detail | What to do about it |
| Row limit | 256 rows returned by default | Turn on Pagination in the action settings and set a threshold |
| File location | OneDrive for Business or SharePoint only | Move the file, or use a gateway for on-premises data |
| Table required | Named Excel Tables only, not ranges | Convert with Ctrl+T and name it properly |
| No Power Query refresh | Cannot refresh a desktop query | Refresh into the file first, or move the transformation into the flow |
| Concurrent edits | A user with the file open can block writes | Schedule outside working hours |
| Action limits | Per-user daily action quota by licence | Filter at source rather than looping through rows |
The row limit is the one that bites hardest, because it fails silently. The flow succeeds and the report is just wrong. Turn pagination on for any table that might grow past a couple of hundred rows.
Making it reliable
A flow that quietly stops working is worse than no flow, because people carry on assuming the report is accurate. Two things make the difference.
Configure run-after failure handling. Add a Send an email action, open its settings, and set it to run only if the previous action fails or times out. Send that to yourself, not to the report recipients.
Check the flow owner. Flows run as the person who created them. If that person leaves and the account is disabled, the flow stops. For anything the business depends on, move it into a solution owned by a service account or a shared owner.
- Add a row count to the email. If the number looks wrong, someone notices. Silent truncation is the real risk.
- Include the run timestamp in the body so recipients know the data is current.
- Handle the empty case. A Condition that sends nothing when there are zero rows is better than a weekly email containing an empty table.
- Name actions meaningfully. Compose 3 is unhelpful when you return to this in a year.
Gear We Recommend
Building and testing flows is easier with a machine that can hold several browser tabs and Excel at once. Here is our admin machine spec.
Browse our Admin Machine picks on AmazonAs an Amazon Associate, TechyGeeksHome earns from qualifying purchases
When to reach for something else
Power Automate is excellent glue and a poor data engine. If the report requires substantial transformation, do that in Power Query inside the workbook or in Power BI and let the flow simply distribute the result. If it needs to run against on-premises systems with no cloud presence, a scheduled PowerShell script is simpler, faster and easier to debug.
Glossary
| Term | What it means |
| Flow | An automated sequence of triggers and actions in Power Automate. |
| Connector | A pre-built integration with a service such as Excel, Outlook or SharePoint. |
| Trigger | The event that starts a flow. A schedule, a new file, an incoming email. |
| Action | A single step within a flow. |
| Pagination | Retrieving results in batches to get past the default row limit. |
| OData filter | The query syntax used to filter rows at the source. |
| Solution | A container that lets flows be owned and moved between environments. |
Frequently asked questions
Do I need an extra licence for Power Automate?
Most Microsoft 365 business plans include the standard connectors, which cover Excel, Outlook, SharePoint and Teams. Premium connectors such as SQL Server or custom HTTP calls need an additional licence.
Can Power Automate read a file on our file server?
Not directly. The Excel connector works with OneDrive for Business and SharePoint. For on-premises data you need an on-premises data gateway, or you move the file to SharePoint, which is usually the better answer.
Why does my report only show 256 rows?
That is the default limit on List rows present in a table. Open the action settings, turn on Pagination and set a threshold above your expected row count.
Can it refresh my Power Query connections?
No, not in a desktop workbook. Refresh the data into the file before the flow runs, or rebuild the transformation inside the flow itself.
What happens to my flows if I leave the company?
They stop when the account is disabled, because flows run as their owner. Anything the business depends on should be placed in a solution with a service account or a co-owner.
Disclosure: this post may contain affiliate links. If you buy through one of them, we may earn a small commission at no extra cost to you. We only recommend products we’ve tested or genuinely rate.
Discover more from TechyGeeksHome
Subscribe to get the latest posts sent to your email.
