P
AI for Supply Chain Analyst

Automate Weekly Report Distribution with Power Automate + Copilot

Tools:Microsoft Power Automate + Excel / Power BI
Time to build:2 hours
Difficulty:Intermediate-Advanced
Prerequisites:Familiar with Excel Copilot or Power BI basics — see Level 2 guides

What This Builds

A scheduled automation that runs every Monday morning, pulls your latest inventory data, formats it, and emails the weekly inventory status report to your team's distribution list — automatically, with zero manual intervention. Once built, you get 30+ minutes per week back from report distribution and never miss a report deadline again.

Prerequisites

  • Microsoft 365 account with Power Automate access (included in most M365 Business plans — check at make.powerautomate.com)
  • Your weekly inventory report in either Excel Online (OneDrive/SharePoint) or Power BI Service
  • The distribution list (email addresses) for your weekly report recipients
  • Comfortable with Excel or Power BI at a basic level
  • Cost: Included with Microsoft 365 Business Standard or above

The Concept

Power Automate is Microsoft's workflow automation tool — think of it as a set of dominoes you line up once and then knock over on a schedule. You set up: "Every Monday at 8am → Export my inventory report → Attach it to an email → Send to my team." After setup, it runs every week without you doing anything. Copilot helps you set up the automation steps using plain language instead of requiring you to know technical configuration.

Build It Step by Step

Part 1: Set up your report for automation

First, your report needs to live somewhere Power Automate can access it — not on your local desktop.

For Excel reports:

  1. Move your inventory report workbook to OneDrive or a SharePoint folder your organization uses
  2. Make sure it's the "live" version you update weekly
  3. Note the file path (e.g., https://company.sharepoint.com/sites/SupplyChain/Shared Documents/Weekly Inventory Report.xlsx)

For Power BI reports:

  1. Your report must be in Power BI Service (the online version at app.powerbi.com), not just Desktop
  2. Note the report name and workspace it's in

Part 2: Open Power Automate and create a new flow

  1. Go to make.powerautomate.com and log in with your Microsoft account
  2. Click Create → Scheduled cloud flow
  3. Name it: "Weekly Inventory Report Distribution"
  4. Set the schedule: Every 1 week on Monday at 8:00 AM (adjust to your time zone)
  5. Click Create

Part 3: Use Copilot in Power Automate to build the flow

  1. In the flow editor, click the Copilot button (sparkle icon, right side panel)
  2. Describe what you want to do:

"I want to send a weekly email every Monday at 8am. It should: export my Power BI report called 'Weekly Inventory Status' from my 'Supply Chain' workspace as a PDF, then send it as an email attachment to a list of recipients with the subject line 'Weekly Inventory Status — [today's date]'."

Copilot will suggest the flow steps. Review and accept them.

If using Excel instead of Power BI: "I want to send a weekly email every Monday at 8am. Get the file 'Weekly Inventory Report.xlsx' from my OneDrive folder '/Supply Chain Reports/', attach it to an email, and send to these recipients: [list of email addresses]. Subject line: 'Weekly Inventory Status — [today's date]'."

Part 4: Configure the email step

After Copilot builds the flow, click on the Send an Email step to configure it:

  • To: Enter your distribution list email addresses (comma-separated)
  • Subject: Weekly Inventory Status — @{formatDateTime(utcNow(),'MMMM d, yyyy')} (This dynamically inserts today's date — keep this expression exactly as written)
  • Body: Enter a brief standard intro. Use Copilot to help: "Write a 2-sentence weekly email introduction for an inventory status report that goes to our supply chain team."
  • Attachment: The Power BI export or Excel file should already be mapped from the previous step

Part 5: Test the flow

  1. Click Save to save your flow
  2. Click Test → Manually → Run flow
  3. Check your email — you should receive the report within a minute
  4. If it fails, click the failed run to see the error, then use Copilot Chat: "My Power Automate flow failed with this error: [paste error]. How do I fix it?"

Part 6: Activate and monitor

Once the test passes, the flow will run automatically every Monday at 8am. Check the Run History in Power Automate occasionally to verify it's running successfully.

Real Example: Weekly Inventory Report for 8-Person SC Team

Setup:

  • Report: Power BI report in "Supply Chain Ops" workspace
  • Recipients: 8 people including VP Supply Chain, 3 category managers, 2 planners, DON, and Finance
  • Schedule: Monday 7:30am (before the 9am SC team standup)

Flow steps built:

  1. Scheduled trigger: Every Monday at 7:30am
  2. Export Power BI report to PDF
  3. Send email: Subject "Weekly SC Inventory Status — [date]", Body: "Please find this week's inventory status report attached. Key risk items are highlighted on pages 2-3. Reply to this email if you have questions.", Attachment: the PDF

Time saved: 30 minutes/week × 50 weeks = 25 hours/year recovered from manual report distribution

What to Do When It Breaks

  • Flow runs but email doesn't send → Check the email step for authentication issues. Log out and back into Power Automate and reconnect your Outlook connection.

  • Power BI export fails → Verify the report name and workspace name are exactly as they appear in Power BI Service (case-sensitive). Also check that your Power Automate account has access to that Power BI workspace.

  • Date formula shows wrong date → Use @{formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'),'MMMM d, yyyy')} to convert to your local time zone.

Variations

  • Simpler version: Just send a standard weekly email reminder to yourself with a link to the report, instead of exporting and attaching it.
  • Extended version: Add a Copilot in Power Automate step to auto-generate a 3-sentence narrative summary of the week's KPIs from your data before sending.

What to Do Next

  • This week: Build and test the automation with just one recipient (yourself) first.
  • This month: Add a second automated report — supplier scorecard distribution or monthly KPI deck.
  • Advanced: Add a conditional step: "If any SKU has less than 7 days of supply, add a RED FLAG flag in the email subject line."

Advanced guide for supply chain analysts. Power Automate is included in Microsoft 365 Business Standard and above. The automation built here requires no coding — just configuration.