How to Automatically Export Data from Power BI to Excel
Overview
Power BI is a powerful business intelligence tool that allows you to visualize and analyze data from various sources. While Power BI offers great visualization and analysis capabilities, you may sometimes need to export the data to Excel for further manipulation or sharing purposes. Here’s a step-by-step guide on how to automatically export data from Power BI to Excel.
Step 1: Install and Set Up Power Automate
Power Automate (formerly known as Microsoft Flow) is a cloud-based service that allows you to create automated workflows between various applications and services, including Power BI and Excel. You will need to have Power Automate installed and set up to proceed with the following steps.
Step 2: Create a New Flow
Open Power Automate and click on “Create” to start a new flow. Choose the “Automated – from blank” template to create a flow from scratch.
Step 3: Set Up the Trigger
The trigger determines when the flow will be initiated. In this case, we want the flow to trigger whenever new data is added to a Power BI dataset. Search for and select the “Power BI – When a new row is added” trigger.
Step 4: Connect to Power BI
Connect your Power BI account to Power Automate by signing in with your Power BI credentials. Select the workspace and dataset that contains the data you want to export to Excel.
Step 5: Add an Action
Click on “+ New Step” in your flow and search for “Excel – Create row”. Select the “Excel – Create row” action to add it to your flow.
Step 6: Connect to Excel
Connect your Excel account to Power Automate by signing in with your Excel credentials. Select the workbook and worksheet where you want to export the Power BI data.
Step 7: Map Power BI and Excel Columns
In this step, you need to map each Power BI column to the corresponding Excel column. Select the Power BI dataset column from the dropdown and map it to the appropriate Excel column.
Step 8: Save and Test the Flow
Save your flow and give it a meaningful name. You can then test the flow by adding a new row of data to your Power BI dataset. Power Automate will automatically export the data to Excel according to your flow configuration.
Example:
Let’s say you have a Power BI dataset that contains sales data, and you want to automatically export the sales data to an Excel workbook whenever a new sale is recorded.
- Power BI Dataset Columns: Date, Product, Quantity, Price
- Excel Worksheet Columns: A (Date), B (Product), C (Quantity), D (Price)
You would map the Power BI dataset columns to the corresponding Excel columns in step 7, i.e., Date -> A, Product -> B, Quantity -> C, Price -> D.
Whenever a new sale is added to your Power BI dataset, this flow will automatically create a new row in the specified Excel worksheet with the corresponding sales data.