Want to automate Power BI reports in your organization? Focus on the following areas:
We have previously written an article about automating data extraction and transformation in Power BI. This article will focus on automating data refresh and distribution of Power BI reports.
There are several methods to automate Power BI reports: using Power BI Service, Power Automate, XMLA endpoints, and REST API. We will dive into practical use cases and step-by-step guides for implementing these automation techniques.
Whether you’re a business analyst, data engineer, or IT professional, this comprehensive guide will equip you with the knowledge and tools needed to enhance your Power BI reporting capabilities through automation.
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:
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.
Ensure your dataset is published to the Power BI Service. 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 Automate (formerly Microsoft Flow) allows you to create automated workflows that can include refreshing Power BI datasets as part of a broader automation process. We can schedule the refresh of Power BI reports using Power Automate. Here’s the process to configure it:
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.
Power BI has some limitations when it comes to scheduled refresh, particularly for datasets that are hosted on Power BI Service. Here are the key limitations:
Refresh Frequency:
Data Size:
Exporting a Power BI report into PDF or other formats is a common business requirement. This process can be automated 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. Let’s understand the steps to configure a flow to implement this
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.
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.
Click on New Step, search for Send an email, and select the appropriate email action (e.g., Send an email (V2) for Outlook). Provide details in 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 manual intervention.
In addition to refreshing datasets and exporting Power BI reports to PDF, there are several other tasks that can be automated using Power Automate. You can read more about them in our Power Automate Power BI Integration article. For now will briefly mention them 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
Power BI Automated Email Reports
In case you are wondering whether Power BI can send automated emails without the use of Power Automate, the answer is yes! There are 2 useful Power BI features that help with this task.
The subscriptions feature in Power BI allows users to receive report updates directly in their email inbox at specified intervals. This feature ensures that stakeholders are consistently informed about the latest data insights without manually checking the reports. Let’s understand how to set up subscriptions:
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. 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. 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 set up notifications that are triggered when data in a report or dashboard reaches a specific threshold or condition. This feature is useful for monitoring key metrics and receiving timely alerts about important changes in the data. Let’s explore how to set up data alerts.:
Navigate to the 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 alert 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.
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:
powerbi://api.powerbi.com/v1.0/myorg/{workspaceName}.
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.
