DEV Community

AutomateHQ
AutomateHQ

Posted on

How I Automated My Weekly Reports in Power BI Using Power Automate

#powerautomate #powerbi #automation #microsoft365

Every Friday at 3:00 PM, thousands of professionals around the world do the exact same thing: they open an ERP system, export a CSV, paste it into an Excel template, refresh a pivot table, save it as a PDF, and email it to their boss.

It takes 45 minutes. It is mind-numbingly boring. And it is completely unnecessary.

If you are using Microsoft 365, you have all the tools required to make this happen automatically while you are drinking your Friday afternoon coffee. Here is exactly how I automated my weekly reporting using Power BI and Power Automate.

The Goal

We want to achieve a zero-touch reporting pipeline:

  1. Data updates automatically.
  2. A snapshot of the dashboard is captured.
  3. The snapshot is emailed to stakeholders on a schedule.

Step 1: Automate the Data Refresh (Power BI)

The first step is getting your data into Power BI and ensuring it stays fresh.

If you are exporting CSVs manually, stop. Connect Power BI directly to your data source. Power BI has native connectors for Salesforce, Dynamics, SQL databases, SharePoint lists, and hundreds of other platforms.

Once connected:

  1. Publish your report to the Power BI Service (online).
  2. Go to the dataset settings.
  3. Set up Scheduled Refresh.
  4. Configure it to refresh every Friday at 2:00 PM (giving it time to complete before the 3:00 PM email).

Note: You will need a Power BI Pro license and potentially an On-Premises Data Gateway if your database is not in the cloud.

Step 2: Build the Power Automate Flow

Now that the data is refreshing automatically, we need to distribute it. We will use Power Automate to handle the delivery.

  1. Open Power Automate and create a Scheduled cloud flow.
  2. Set the schedule to run every Friday at 3:00 PM.
  3. Add the action: Export To File for Power BI Reports.

Configuring the Export Action

  • Workspace: Select the workspace where your report lives.
  • Report: Select your report.
  • Export Format: Choose PDF (or PPTX if they prefer slides).
  • Pages: You can specify exactly which page of the dashboard to export if you don't want to send the whole thing.

Crucial Requirement: The "Export To File" action requires your Power BI workspace to be backed by a Premium capacity or Premium Per User (PPU). If you only have a standard Pro license, this specific action will fail.

Step 3: Deliver the Report

The final step is getting that PDF into the hands of your stakeholders.

Add a new action: Send an email (V2) (Office 365 Outlook).

  • To: Add your boss, the team, or a distribution list.
  • Subject: Weekly Performance Report - @{utcNow('yyyy-MM-dd')} (Using dynamic content to add today's date).
  • Body: Add a brief, professional message. "Hi team, please find attached the automated weekly performance report."
  • Attachments Name: WeeklyReport.pdf
  • Attachments Content: Select the File Content dynamic variable from the previous Power BI export action.

The "Poor Man's" Alternative (No Premium Required)

If you read Step 2 and realised you don't have Power BI Premium, don't panic. There is a built-in feature in Power BI Service that does something very similar without Power Automate.

It's called Email Subscriptions.

  1. Open your report in the Power BI Service.
  2. Click Subscribe to report in the top toolbar.
  3. Click Add new subscription.
  4. Enter the email addresses, set the schedule (Friday at 3:00 PM), and choose to include an image preview and a link to the report.
  5. If you have a Pro license, you can also attach a full PDF.

It is less customisable than Power Automate, but it takes 60 seconds to set up and works brilliantly.

Take Your Automation Further

Automating reports is the gateway drug to business automation. Once you realise you don't have to do manual data entry, you start looking at every process in your business differently.

If you want to automate more than just your reports, I have packaged my best workflows, templates, and governance guides into one bundle.

Get the Complete Microsoft 365 Automation Bundle ($39)

It includes ready-to-use Power Automate flows that will save you hours every week.

What is the most tedious manual report you have to run right now? Let me know in the comments.

Top comments (0)