Power BI report automation is a process of automatically generating, distributing, exporting to Excel and generating PDFs from Power BI. It is common to use Power Automate to automate these tasks but a lot of automation is achievable without any third-party tools.
Power Automate (formerly Microsoft Flow) allows you to create automated workflows that contain a trigger and an automated action. Power Automate integrates really well with other Microsoft technologies which is why it is a common choice for automating Power BI reports.
We often help our clients automate their reports during our Power BI consulting projects. On average report automation projects save our clients 4-10 working hours per week.
In this article we will cover how to automate Power BI reports including:
Scheduling data refreshes is fundamental to keeping your Power BI reports updated with the latest information. There are two primary ways to automate the refresh of Power BI reports:
You can use Power Automate to refresh Power BI datasets in 2 simple steps:
Open Power Automate and click on Create a Flow. Choose Scheduled Cloud Flow, then set the desired time for the flow to run (daily, weekly, or monthly) and click Create.
After the flow is created, add an action by selecting Power BI and searching for the “Refresh a Dataset” action. Choose the workspace and semantic model you want to refresh, then click Save.
A big advantage of refreshing Power BI datasets with Power Automate is that you can select different triggers for starting your dataset refresh. The simplest trigger is a timer but you can also launch a Power BI dataset refresh once a new row is added to your Sharepoint list or a database.
Please note that all the license-based Power BI limitations still apply to dataset refreshes with Power Automate including:
Refresh Frequency:
Data Size:
The Power BI Service provides a straightforward method to schedule data refreshes for your reports. This approach is ideal for users who want to automate the refresh process without additional integrations.
In the Power BI Service, go to the workspace containing your dataset, and click on settings:
Make sure the data source credentials are configured correctly to allow scheduled refresh. Under the Scheduled refresh section, toggle the Keep your data up to date option to On. Specify the refresh frequency (daily, weekly, etc.) and the time when the refresh should occur. Save the settings to apply the scheduled refresh.
Power BI Export to PDF or other formats is a common business requirement when you want to share reports outside of your organization. This process can be automated by using Power Automate, allowing business users to receive a Power BI report in PDF format in their Outlook inbox at a specific time each day.
You can automate Power BI export to PDF with Power Automate in 3 steps:
2. In the flow designer, click on “New Step”, search for “Power BI”, and select the “Export to File For Power BI Reports” action. Choose the workspace and report you want to export. Select PDF as the export format. Optionally, you can specify specific pages or bookmarks to include in the export.
3. Click on “New Step”, search for Send an email, and select the appropriate email action (e.g., Send an email (V2) for Outlook). Populate all the sections including: To, Subject and Body. Add previous step output to the attachment input as follows:
By following these steps, you can automate the process of exporting Power BI reports to PDF and sending them via email, ensuring that your stakeholders always have access to the latest data insights without the need for a Power BI license or a manual intervention.
It is quite easy to automatically send emails with Power BI reports every time your Power BI dataset is refeshed! There are 2 useful Power BI features that help with this task.
The subscriptions feature in Power BI enables admins to send email with Power BI reports to their audience at specified intervals. This feature ensures that stakeholders are consistently informed about the latest data without manually checking the reports.
Navigate to the Power BI service and open the report or dashboard you want to subscribe to. Click on the Subscribe button (bell icon) at the top of the report or dashboard.
Enter a name for the subscription and then add the email addresses of the recipients who should receive the report updates. Set the frequency of the subscription (e.g., daily, weekly, monthly) and specify the time when the report should be sent. Optionally, add a custom message to include in the email. Once you are done configuring your subscription click Save and close to activate the subscription.
Once the subscription is set up, the recipients will receive email updates with the latest version of the report or dashboard according to the specified schedule.
Data alerts in Power BI allow users to send email with Power BI reports when data in a visual reaches a specific threshold or condition. This feature is useful for monitoring key metrics and receiving timely alerts about important changes in the data.
To set up data alerts navigate to Power BI service and open the report or dashboard containing the visual you want to monitor. Click on the visual that you want to set an alert for. Click on the … (more options) menu on the visual, and select Manage alerts.
Define the condition that will trigger the alert (e.g., when a value exceeds a certain threshold).Set how often you want to be notified (e.g., once an hour, once a day). Specify the email addresses of the recipients who should receive the email or teams notifications. Click Save and close to activate the alert.
Once the data alert is set up, Power BI will monitor the specified visual and send email notifications to the recipients when the alert condition is met.
It is a common requirement to automatically export Power BI reports to Excel to further explore the data there. Excel users often want to leverage the data from Power BI to build their own reports or analyze data in their own specific way.
There are 2 ways to automatically export data from Power BI reports to Excel:
You can read the step-by-step instructions for exporting the data from Power BI dataset using Power Automate in our guide.
A short explanation is that you can use the “run a query against a dataset” action in Power Automate to export the data. You can then insert the result of this query into Excel, Sharepoint or even an outlook email.
The query can be generated in Power BI desktop by opening performance analyzer. Your query will be a DAX statement which you can copy and paste to your Power Automate flow.
The final step is to create a data operation with the dataset exported from a query. You can use the “create csv table” action to export the data to a spreadsheet.
The Power BI Analyze in Excel feature allows you to export the data automatcally fromm your Power BI reports without using third-party tools like Power Automate.
To access it go to Power BI service, find the semantic model for your report and click “Analyze in Excel” at the top. This will generate an Excel file for you which is automatically connected to your Power BI Semantic model.
Once you open the downloaded Excel file, you will see a blank pivot table which you can populate by using the tables and columns in pivot table field. Those tables and columns come from your semantic model and also contain all of your measures.
You can then manually refresh the data in the table by right clicking on it and clicking “refresh”. Alternatively you can schedule automatic data refresh by going to PivotTable Analyze -> Refresh -> Connection Properties -> Refresh Settings.
There are many Power BI actions that you can do with Power Automate. You can read more about them in our Power Automate Power BI Integration article. You will find the full list below:
Action: This action allows you to add new rows to an existing Power BI dataset. It is useful for dynamically updating your dataset with new data in real-time.
Use Case: For instance, if you have a dataset tracking sales data, you can use this action to add new sales records as they occur, ensuring your report reflects the latest information.
Action: This action enables you to export a Power BI paginated report to a specific file format, such as PDF or Excel.
Use Case: For instance, you can schedule a flow to automatically export monthly financial reports to PDF and distribute them to stakeholders via email.
Action: This action retrieves detailed information about a specific goal in Power BI. It provides insights into the current status, progress, and other key details of the goal.
Use Case: Use this action to monitor the status of business objectives, enabling you to make informed decisions based on the latest goal updates.
Action: This action allows you to execute a JSON query against a Power BI dataset.
Use Case: Use this action to perform specific data queries and retrieve results in JSON format, which can then be used in further actions or analyses.
Action: This action allows you to execute a query against a Power BI dataset.
Use Case: Use this action to run customized queries to extract specific data from a dataset, which can be useful for generating tailored reports or analyses.
Action: This action retrieves information about multiple goals in Power BI.
Use Case: Use this action to gather details about several goals at once, which can help in comparing progress and performance across different objectives within your organization.
Action: This action retrieves information about scorecards in Power BI.
Use Case: Use this action to access details about scorecards, which are collections of goals and KPIs, providing a holistic view of performance across various metrics
Action: This action allows you to update an existing check-in for a goal in Power BI.
Use Case: Use this action to modify the details of a check-in, such as progress or status updates, ensuring that the information remains accurate and up-to-date.
Action: This action allows you to update an existing goal in Power BI.
Use Case: Use this action to modify the details of a goal, such as its target or description
Automating Power BI reports using XMLA and REST API endpoints can significantly enhance your reporting capabilities, ensuring that your reports are always up-to-date and accessible. These methods provide powerful ways to interact with Power BI datasets, automate tasks, and integrate with other systems. Let’s explore how to use XMLA and REST API endpoints to automate Power BI reports.
XMLA (XML for Analysis) endpoints allow you to interact with Power BI datasets using the same protocol as SQL Server Analysis Services (SSAS). This method is ideal for large-scale data operations and provides a robust way to manage and query datasets.
Steps to Use XMLA Endpoints:
XMLA is particularly useful when you need to refresh a specific table within a semantic model instead of the entire model. You can use the following TMSL script to refresh your table via XMLA:
{
“refresh”: {
“type”: “full”,
“objects”: [
{
“database”: “DB Name”,
“table”: “Table Name”
}
]
}
}
Note: XMLA operations are supported exclusively in either Premium or Fabric workspaces.
The Power BI REST API provides a programmatic way to interact with Power BI resources, allowing you to automate tasks such as updating datasets, modifying reports, and managing access to workspaces.
Use the Power BI REST API endpoints to perform various tasks. Some common actions include:
Let’s say if someone needs to retrieve a list of all reports in a workspace, they can use the following PowerShell script with the Power BI REST API:
$headers = @{
Authorization = “Bearer $accessToken”
}
$apiUrl = “https://api.powerbi.com/v1.0/myorg/groups/$workspaceId/reports”
$response = Invoke-RestMethod -Method Get -Uri $apiUrl -Headers $headers
# Output the list of reports
$response.value | Select-Object -Property id, name
You can refer the documentation and all end points for Power BI Rest API here:
Power BI REST APIs for embedded analytics and automation – Power BI REST API | Microsoft Learn
Automating Power BI reports offers numerous advantages for businesses, enabling them to stay ahead in today’s fast-paced and data-driven environment.
By implementing automation techniques such as scheduled refreshes, Power Automate workflows, XMLA endpoints, and REST API integrations, organizations can ensure that their reports are always up-to-date with the latest data.
