How to Schedule Power BI Data Exports to Excel

Manually exporting data from your Power BI dashboards every morning is a routine that quietly steals your time. What starts as a quick five-minute task easily turns into an hour of downloading, formatting, and emailing files before you've even had your first coffee. This article will show you how to break that cycle by scheduling and automating your Power BI data exports directly to Excel, saving you time and ensuring your team always has the latest data.

Why You Should Automate Power BI Exports

If you're pulling data from Power BI into Excel, you're likely doing it for a specific reason - sharing it with a stakeholder who prefers spreadsheets, archiving daily snapshots, or integrating the data into another workflow. Manually handling this process, however, introduces several problems:

• It's Time-Consuming: Logging in, applying filters, exporting a visual, and formatting the file takes time. Doing this daily or weekly adds up to hours you could be spending on actual analysis.
• It's Prone to Errors: A simple mistake, like choosing the wrong filter or exporting the wrong visual, can lead to incorrect data and flawed decisions. Repetitive manual tasks are a breeding ground for human error.
• It Creates Data Delays: Your team is always working with data that's as old as your last export. Decisions are made based on information that might be hours or even days out of date, creating a lag between insight and action.
• It Relies on You: If you're out of the office or busy with another project, the reports don't get sent. This creates a bottleneck where others are waiting on you just to get the numbers they need.

By investing a small amount of time to set it up once, you can eliminate this manual drudgery permanently with Microsoft's Power Automate.

Method: Schedule Exports with Power Automate

Power Automate (formerly known as Microsoft Flow) is the best and most flexible way for most users to automate Power BI exports. It connects different apps and services in the Microsoft ecosystem, allowing you to create automated workflows. Using it, you can run a query against your Power BI dataset on a schedule and save the results as an Excel-compatible file in OneDrive or SharePoint.

Prerequisites Before You Begin

Before jumping into the steps, make sure you have a few things in place:

• A Power BI Pro or Premium License: The Power Automate connector for Power BI requires at least a Pro license to work.
• Access to Power Automate: This is typically included with most Microsoft 365 and Office 365 business subscriptions.
• A Published Report: The report you want to export from must be published to a Power BI service workspace. Automation will not work on reports that are only in "My Workspace."
• A Destination: You'll need a location to save your exported file, such as a specific folder in OneDrive or a SharePoint document library.

Step 1: Create Your Scheduled Flow in Power Automate

First, log into Power Automate. Once you're in, we'll create a new "flow" from scratch that runs on a schedule.

1. On the left-hand navigation pane, click Create.
2. Select Scheduled cloud flow from the options.
3. A dialog box will appear. Give your flow a descriptive name, like "Daily Sales Data Export to SharePoint."
4. Set your starting date and time, and then define the schedule. For a daily report, you'd set it to repeat every 1 Day. You can choose from many intervals, including hourly, weekly, or monthly.
5. Click Create.

This creates an empty flow with a "Recurrence" trigger, which is the schedule you just defined.

Step 2: Add the Power BI Action to Query Your Data

Now, we'll tell the flow to query your Power BI dataset. Don't worry if you don't know SQL or DAX - there's an easy way to get the exact query you need.

1. Click the + New step button below the Recurrence trigger.
2. In the search box, type "Power BI" and select the Power BI connector.
3. From the list of actions, choose Run a query against a dataset. If this is your first time, you may need to sign in to authenticate your Power BI account.
4. Now, configure the action:

Pro-Tip for Getting the DAX Query: The easiest way to get the query for a specific table or visual is by using the Performance Analyzer in Power BI Desktop.

1. Open your report in Power BI Desktop.
2. Go to the View tab in the ribbon and check the box for Performance Analyzer.
3. A new pane will appear. Click Start recording.
4. Interact with the visual you want to export (e.g., click on it or refresh it). The visual's elements will appear in the Performance Analyzer pane.
5. Expand the element for your visual and look for "DAX query." Click Copy query.

Go back to Power Automate and paste this code into the Query Text box. You can also use a very simple DAX query to export an entire table, like:

EVALUATE 'YourTableName'

Step 3: Format the Data into a CSV Table

The "Run a query against a dataset" action returns data in a JSON array format. We need to convert this into a format that Excel can easily read, like CSV (Comma Separated Values).

1. Click + New step.
2. Search for Data Operation and select the Create CSV table action.
3. In the From field, we'll use dynamic content from our previous Power BI step. Click inside the field, and the dynamic content pane should appear on the right.
4. Select First table row from the "Run a query against a dataset" section. This tells the flow to take the rows of data from our DAX query result.

You can leave the Columns option as Automatic, and Power Automate will handle creating the headers and rows for you. This step effectively transforms the raw Power BI data into a structured CSV format ready to be saved.

Step 4: Save the CSV File to OneDrive or SharePoint

The final step is to save our newly created CSV content as a file in the cloud storage location of your choice. The steps for OneDrive and SharePoint are nearly identical.

1. Click + New step.
2. Search for OneDrive for Business or SharePoint and select the Create file action for that service.
3. Select the destination folder by clicking the folder icon in the Folder Path field.
4. For the File Name, it's best to use a dynamic name so each export is unique and doesn't overwrite the previous one. A simple way to do this is to add a timestamp. You can use an expression like: formatDateTime(utcNow(), 'yyyy-MM-dd')-Sales-Data.csv. This will generate a filename like "2023-10-27-Sales-Data.csv".
5. For the File Content, use the dynamic content from the previous step. Select Output from the "Create CSV table" section.

That's it! Your flow is complete. It will now run on the defined schedule, query Power BI, create a CSV, and save it to your specified folder.

Step 5: Test Your Flow

Before you let the schedule take over, it's a good idea to test your flow to make sure everything works.

1. In the top-right corner of the flow editor, click Save.
2. Once saved, click Test.
3. Choose the Manually option and click Test, then Run Flow.

Power Automate will show you a live run of the flow. If all steps show a green checkmark, the flow was successful. Check your OneDrive or SharePoint folder to confirm that the file was created correctly.

Alternative Method: Report Subscriptions (Paginated Reports Only)

If your organization uses Power BI Premium and you need to export a pixel-perfect, highly formatted report, you can use built-in subscriptions for Paginated Reports. Unlike standard Power BI reports, paginated reports are designed for printing and exporting and can be exported as real XLSX files.

The process is much simpler, but the requirements are higher:

1. Navigate to the published paginated report in the Power BI Service.
2. Click the Subscribe icon in the toolbar.
3. Click + Add new subscription.
4. In the settings pane, configure your schedule (daily, weekly, etc.).
5. Under "Format," select Excel.
6. Enter the recipient email addresses and customize the email subject and message.
7. Click Save and close.

While straightforward, this method is only viable for users with a Premium license and who are working with paginated reports specifically designed for this type of export.

Final Thoughts

Setting up an automated workflow in Power Automate moves you from being a manual data processor to an efficient analyst. By removing the repetitive task of exporting from Power BI, you free up valuable time to focus on interpreting data and finding insights, not just moving it around from one platform to another.

While automating exports to static files like Excel is a great step, we built Graphed to help teams move beyond this manual reporting loop entirely. Instead of exporting data to a spreadsheet, you can connect tools like Google Analytics, Salesforce, and a dozen others to create live, real-time dashboards using plain English. Just ask "create a dashboard comparing ad spend vs. revenue last month," and the dashboard is built for you in seconds, always up-to-date, with no exports needed.