How to Automate Power BI Reports

Spending hours every Monday morning manually refreshing your Power BI reports and sending them out is a frustrating and repetitive ritual. The good news is that you can completely eliminate it. Power BI has powerful, built-in automation features that can handle both the data updates and the report distribution for you.

This tutorial will walk you through exactly how to set up automated refreshes and subscriptions in Power BI. You'll learn how to get your data updated on a schedule and deliver reports directly to your team's inbox, reclaiming hours of your valuable time.

Why Automate Your Power BI Reports?

Before getting into the steps, it's worth appreciating why this is so valuable. Manually clicking "Refresh" is not just tedious, it creates unnecessary friction in your workflow. Automating your reports offers several significant advantages:

• Saves Time and Effort: This is the most obvious benefit. Automating frees you from a repetitive, low-value task. The hours once spent exporting files and sending emails every day, week, or month can now be spent on actual analysis and strategic work.
• Improves Data Freshness and Consistency: Scheduled refreshes ensure your team is always looking at the most up-to-date information possible. It removes the risk of someone acting on an old report or a version with stale data. Automation creates a single source of truth that everyone can trust.
• Reduces Human Error: Manual processes are prone to mistakes. You might forget an email, pull data from the wrong date range, or get interrupted and never finish the task. Automation runs on a precise schedule, delivering the correct data to the right people every time without fail.
• Empowers Your Team: When stakeholders know they can rely on receiving a report at 8 AM every morning, they can make data-driven decisions part of their daily routine. They don't have to chase you down for updates, making the entire organization more efficient and data-literate.

The Heart of Automation: Scheduled Refresh

The foundation of all Power BI report automation is the scheduled refresh. This feature lives inside the Power BI Service (the web-based version at app.powerbi.com), not the Power BI Desktop application you use to build reports.

Once you publish your report from the Desktop app to the Power BI Service, a corresponding dataset is created. The scheduled refresh feature tells this dataset to automatically reconnect to its underlying data sources - be it an Excel file, a SQL database, or Google Analytics - at intervals you define. This keeps the data in your published report fresh without you ever having to open Power BI Desktop again to hit refresh.

To use this feature, you'll need a Power BI Pro or Power BI Premium license. Free licenses do not support gateways or scheduled refreshes for most data sources.

Step-by-Step Guide to Scheduling Data Refreshes

Getting your automatic refresh up and running involves a few key steps focused on making sure the Power BI Service can securely connect to your data sources when you're not around.

Step 1: Understand Your Data Source Connectivity

The first thing to determine is where your data lives. Power BI handles different data sources in slightly different ways.

• Cloud Data Sources: These are sources that are already online, like SharePoint Online, Azure SQL Database, Google Analytics, or Salesforce. The Power BI Service can connect to them directly over the internet with just your login credentials. Automation for these is generally straightforward.
• On-Premises Data Sources: These are sources that exist within your organization's private network. Common examples include a local SQL Server, a file server, or an Excel workbook saved on your computer's C: drive. The Power BI Service cannot reach these directly. To bridge this gap, you need a tool called the On-Premises Data Gateway.

Step 2: Install and Configure the On-Premises Data Gateway (If Needed)

If any of your report's data sources are on-premises, you must install a gateway. The data gateway is a small piece of software that acts as a secure bridge, letting the cloud-based Power BI Service send queries to your local data sources and get back results. Think of it as a guarded tunnel passing data from your network to the cloud.

Here’s the setup in a nutshell:

1. Download the On-Premises Data Gateway (standard mode) directly from the Microsoft Power BI website.
2. Install it on a computer that is connected to the same network as your data sources and, crucially, is always powered on and connected to the internet. A local server is ideal, but a dedicated desktop can also work.
3. Follow the installation wizard, signing in with your Power BI account when prompted. This registers the gateway to your Power BI tenant.

Once installed, the gateway runs as a service in the background, waiting for refresh requests from the Power BI Service.

Step 3: Manage Your Data Source Credentials

With the report published and the gateway running (if needed), you need to securely store the credentials Power BI will use to access each data source.

1. Log in to the Power BI Service (app.powerbi.com).
2. In the left-hand navigation pane, find your Workspace and go to the Datasets + dataflows tab.
3. Find the dataset for your report, click the three-dot menu (...) and select Settings.
4. Expand the Data source credentials section. You will see a list of every source used in your report.
5. For each one, click Edit credentials. Here you will securely provide the necessary usernames, passwords, API keys, or use OAuth2 to sign in. Power BI encrypts and stores these for the refresh process.
6. If you are using a gateway, you will also need to map your cloud dataset to the on-premises data source via the gateway under the Gateway connection section.

This is a one-time setup. Once Power BI has the correct credentials, it can use them for all future refreshes without asking again (unless a password expires).

Step 4: Set the Refresh Schedule

This is the final step in automating the data update. In the same dataset settings page, find the Scheduled refresh section.

1. Toggle the "Keep your data up to date" switch to On.
2. Choose the Refresh frequency. Typically, this is Daily, but Weekly is also an option.
3. Select your Time zone to ensure the refresh happens at the correct local time.
4. Under Time, click Add another time to specify when the refresh should run. For example, you can set it to run at 7:00 AM so the data is fresh by the time your team starts their day.

With a Power BI Pro license, you can schedule up to 8 refreshes per day. With Power BI Premium, you can schedule up to 48. Hit Apply, and your data will now refresh automatically without any further action from you.

Automating Report Distribution with Subscriptions

Getting your data to update automatically is just one part of the puzzle. The next goal is getting the finished report in front of your stakeholders without manually exporting and emailing PDFs.

This is where Email Subscriptions come in handy. You can subscribe yourself and others to receive an email with an image of the report and a link to view it in the Power BI Service.

How to Set Up a Subscription

1. Navigate to the report you want to share in the Power BI Service.
2. In the top menu bar, click Subscribe to report.
3. Click Add new subscription.
4. Give your subscription a name and type in the email addresses of the recipients (they must be in your organization's network). You can also send to an entire user group.
5. Write a clear Subject and an optional clarifying message for the email.
6. Choose which report Page to include. The default is the page you were just on, but you can change it.
7. Set the Frequency (Hourly, Daily, Weekly, etc.), scheduled time, and start/end dates for the subscription.
8. Perhaps the most useful feature: you can check a box to Also include an attachment of the report as a PDF or PowerPoint file, allowing stakeholders to view it without visiting the Power BI Service at all.
9. Click Save and close. The email subscription is now active.

You can create multiple subscriptions for the same report. For example, you might create a daily subscription for the project team that includes all pages and a separate weekly subscription for leadership C-Suite that only includes the summary page attached as a PDF.

Monitoring Your Automations

Once everything is set up, you aren't just flying blind. Power BI provides a way to check on the health of your automatic refreshes.

In your dataset settings, click on Refresh history. Here you'll see a log of every scheduled and on-demand refresh attempt, with a status of Completed or Failed. If a refresh fails, Power BI will often provide an error code or message explaining why. Common reasons include:

• Expired data source credentials (e.g., you changed a database password).
• The on-premises gateway is offline (the machine it’s on was turned off or lost internet).
• A change was made to the underlying data source, like a column name being changed or a table being deleted.

In the "Scheduled refresh" settings, make sure the box for "Send refresh failure notifications to me" is checked so you're immediately alerted if something goes wrong.

Final Thoughts

By using Power BI’s built-in scheduled refresh and subscription features, you can create a completely hands-off system for data reporting. This simple automation moves your reporting from a recurring manual chore into a reliable and efficient workflow, freeing up your time to focus on deriving insights from the data, not just pushing it around.

Setting this all up is a huge leap forward, but what if you could bypass configuring gateways and scheduling refreshes altogether? We built Graphed to streamline this whole process. Instead of working through different screens, you can just ask for a report in plain English, like "show me our top marketing channels by conversions this quarter," and Graphed instantly builds a real-time dashboard that updates automatically. Since we connect directly to your apps like Google Analytics, Shopify, and Salesforce, there’s no pipeline to manage - just fresh data, always ready when you need it.