Put the source data in an Excel table, use a scheduled flow to read the previous week, and build a draft from totals your team can verify. Have the flow hold the report if the source is late, incomplete, or out of balance. For the first few runs, let someone approve the draft before it goes to the team.
Here is a practical setup using Excel and Power Automate. The example follows a hypothetical service business reporting the number of jobs it closed last week.
Set up the source table
Start with the export your team already uses to prepare the report. Store the workbook in a location supported by the Excel Online (Business) connector, such as SharePoint or OneDrive for Business, and format the rows as a named Excel table. For this report, keep one row per job with a stable job ID, completion timestamp, team, and final status. If the export includes multiple versions of a job, define which record wins before counting anything.
Record when the export was produced and the date through which it is complete. A workbook that was edited this morning can still contain last week’s incomplete export. Ask the source owner to confirm how the flow can distinguish a finished export with zero jobs from one that has yet to arrive.
Keep a separate copy or snapshot for the reporting run if people edit the live workbook while it is being read. Microsoft’s connector documentation cautions against simultaneous writes by multiple clients. A snapshot also gives the reviewer a consistent set of rows to reconcile.
Schedule the run and define the reporting week
Create a scheduled cloud flow in Power Automate for Monday morning, after the export is expected. Select the team’s business time zone in the recurrence settings. Calculate the previous week’s boundaries in that same time zone. A Monday report might cover midnight at the start of the previous Monday up to, but excluding, midnight at the start of the current Monday.
Use the completion timestamp for a closed-jobs report. Convert timestamps to the reporting time zone before applying the boundaries, or convert the boundaries to the time zone of the source timestamps. Test a job at midnight and a week with a daylight saving change. Keep the start and end dates in the report so readers know which week the numbers describe.
Read the named table with List rows present in a table. Microsoft documents a default limit of 256 rows for this action. Enable pagination when the flow needs more rows and verify the result against a source count, especially with a full week of data. If the workbook already contains calculation logic your team trusts, Microsoft’s Office Scripts tutorial shows how a scheduled flow can run a script and use its returned data.
Reconcile the totals before writing the draft
For this example, each row should represent a unique job. Check for missing IDs, duplicates, invalid timestamps and unexpected status values before calculating totals. Flag records with those issues so the source owner can fix them.
Suppose the reporting window contains 52 rows with valid, unique IDs. Five were canceled and 47 were closed. Of the closed jobs, 30 belong to field service, 15 to installations, and two have no team assigned.
| Check | Expected result in this example |
|---|---|
| Window rows equal all classified rows | 52 = 47 closed + 5 canceled |
| Team subtotals equal the closed total | 47 = 30 field service + 15 installations + 2 unassigned |
| Team is missing for two jobs | Hold the team breakdown until the owner assigns the jobs or approves showing them as unassigned |
Build the draft from the validated counts. Include the period, the source snapshot, and any unresolved flags. A simple template can render the same table every week. If you add AI to draft commentary, supply the checked totals and show the reviewer the source for any explanation. Ask it to flag a missing explanation rather than infer why a team had a busier week.
Give failures a route and prevent repeat sends
When the export is late or the read fails, send the owner a short alert and hold the report. For a confirmed empty export, show zero jobs. For missing data, show which source or check failed in the review queue. Give the reviewer the report draft and a link to the snapshot so they can investigate quickly.
Track delivery by report type and reporting week in a durable record. Record the draft version, approval and delivery status. If the send step times out and you cannot tell whether it delivered, check the delivery record and sent messages before retrying. If the team corrects the report after delivery, label the next version as a correction and identify the changed figures.
Run the first weeks alongside the manual report
Compare the draft against the existing report for a normal week, an empty week, a late export, and a week with more than 256 source rows. Check the boundaries, source count, team subtotals and delivery record. Measure the time spent assembling, checking and fixing the report together. The automation ROI guide explains how to count review and rework when you compare the pilot with the current process.
Use the First AI Project Kit to record the source owner, weekly cutoff, reconciliation checks and who can approve delivery. It includes a playbook and editable worksheet. The first build is ready to scope when the owner can explain what a complete export looks like and what the flow should do when the numbers disagree.