Two potent Microsoft technologies, Power BI and Power Automate, have the potential to completely change how businesses approach data analysis, reporting, and workflow automation. When Power Automate and Power BI are integrated, the possibilities are endless Together, these tools form a powerful team that improves business intelligence, expedites the decision-making process, and maximizes overall productivity.
In this blog, we'll explore how Power BI and Power Automate combine to speed up data processing and automate activities in this article.
Integration of Power BI and Power Automate
Organisations can maximise productivity and unlock the full potential of their data when Power BI and Power Automate are linked. How does this integration function?
Automated Data Refresh: With Power Automate, users can schedule automatic data refreshes in Power BI. This ensures that reports and dashboards always display the most up-to-date information, eliminating the need for manual data updates.
Data-Driven Alerts: Power Automate can be used to monitor data in Power BI and trigger alerts or notifications based on predefined conditions. For instance, if sales fall below a certain threshold, relevant stakeholders can be notified immediately, enabling timely interventions.
Workflow Automation: Power BI insights can initiate workflows in Power Automate. For example, if a report identifies a potential issue, a workflow can be triggered to assign tasks to the appropriate team members and escalate critical matters to higher management.
Export and Distribution: Power Automate can automate the process of exporting Power BI reports and distributing them via email or other communication channels to stakeholders, ensuring seamless information dissemination.
Data Collection and Transformation: Power Automate can collect data from various sources and transform it before loading it into Power BI datasets. This capability streamlines the data preparation process and ensures that data is presented accurately.
Though Power Automate has a lot of triggers and actions which can be used with Power BI we will understand below actions:
Refresh a Power BI Dataset.
Query a Power Bi Dataset
Export a Power BI report in pdf and email.
We have a sales report published in a sales workspace and the data source is an online SharePoint library. The report will be used to demonstrate the below actions in Power Automate.
Refresh a Power BI Dataset
Refreshing a dataset in Power BI using Power Automate (previously known as Microsoft Flow) can be achieved by creating a flow that triggers the dataset refresh at a defined interval or upon a specific event. This integration ensures that your Power BI reports and dashboards always display the most up-to-date data without manual intervention. Here's how you can set up the refresh process using Power Automate:
Prerequisites:
You need to have a Power BI Pro or Premium account to create a dataset refresh in Power BI.
Click on the "Create" button, then select "Scheduled Cloud Flow."
Choose a Trigger. Search and select "Recurrence" as the trigger. This trigger allows you to set a schedule for dataset refresh. Set the Recurrence Schedule. Configure the recurrence schedule according to your requirements. You can choose a specific time of day and the frequency of refreshes (e.g., daily, hourly, weekly).
Add an Action. Search for and select "Power BI" as the action service. Choose "Refresh a dataset" as the action. Sign in to Power BI. If prompted, sign in with the same Power BI account that has access to the dataset you want to refresh.
Select the Workspace and Dataset. Choose the appropriate workspace where the dataset is located. Select the dataset you want to refresh.
Save and Test the Flow:
Before clicking on Test to manually trigger the flow, let's see the dataset's last refresh time:
Go back to the Power BI Workspace to check whether the dataset refreshed:
Our Dataset has been refreshed successfully.
Considerations
Dataset refresh in Power BI is subject to limitations based on your Power BI license type (Pro or Premium) and the dataset's data source. For example, some data sources may have refresh frequency limitations or require a direct connection. (8 Refresh in Power BI Pro and 48 Refresh in Power BI Premium)
Make sure the account used to create the flow has sufficient permissions to refresh the dataset in Power BI and access the data source.
Make sure the dataset is configured with the gateway (On-premises) and authenticated with credentials (Online).
By automating the dataset refresh process using Power Automate, you ensure that your Power BI reports are always up-to-date, providing accurate insights to users without manual intervention. This integration saves time, ensures data accuracy, and empowers better decision-making for your organisation.
Query a Power BI Dataset and send an email
Power BI datasets have a wealth of valuable data that can be used in Power Automate flows. Let’s look at how to retrieve data from a Power BI dataset and email it to a recipient.
Create a Power Automate Flow:
Go to the Power Automate website (https://flow.microsoft.com) and sign in with your Microsoft account.
Click on the "Create" button, then select "Scheduled Cloud Flow."
Choose a Trigger. Search and select "Recurrence" as the trigger. This trigger allows you to set a schedule for dataset refresh. Set the Recurrence Schedule. Configure the recurrence schedule according to your requirements. You can choose a specific time of day and the frequency of refreshes (e.g., daily, hourly, weekly).
Add an Action. Search for and select "Power BI" as the action service.
Search the “Run a query against dataset” action in Power BI and click on it.
Select your workspace and dataset. We need to provide a query text here to explain the data schema for querying.
In order to get a query, open your report in Power Bi Desktop and create a table visual with all the columns required as follows:
Click on the table and go to the optimize tab. Open the performance analyser and click on start recording.
Click on refresh visuals and you will see the time taken by Dax query, visual display and others. Click on copy query.
Once the query is copied, go to MS Flow and paste the query in the query text column.
Click on add action and search “Create a Html table”. This will create a HTML table of the data which can be sent in the body of the email.
Pass the output of the previous action (First Table rows) to the From column.
Now the table is created, let's add an action to send an email to the user. Search Send an email action
Enter the recipient's email address, subject and provide the output of the previous action in the body:
Click on save and test to run the flow manually.
Once the flow runs successfully, go to your Outlook and check the email received from MS Flow.
An email was triggered with all the details configured from the Power BI Dataset.
In order to query a dataset, we can write this custom query to include more columns and filters. Apart from sending emails to the users, the dataset can also be exported to shared locations like SharePoint or One drive.
Export a Power BI Report in pdf and send it via email.
A common workflow with business users consuming Power BI reports is to export the report in PDF/PPT format and send it to stakeholders. This workflow can be automated using MS Flow, let's understand the step-by-step implementation of it:
Click on the "Create" button, then select "Scheduled Cloud Flow."
Choose a Trigger. Search and select "Recurrence" as the trigger. This trigger allows you to set a schedule for dataset refresh. Set the Recurrence Schedule. Configure the recurrence schedule according to your requirements. You can choose a specific time of day and the frequency of refreshes(e.g., daily, hourly, weekly).
Go to Power BI actions and select “Export to file for Power BI reports”
Enter Workspace, Report, Report format(Pdf/ppt) and other details. User can also provide the bookmark name or RLS-related details to limit the visuals on the export.
In order to send emails to users with attachments, go to “Send an email” Action. Enter the mail recipients name, subject and body of the email. Make sure the attachment name ends with the format name (.pdf,.ppt). Add previous action File Content to the Attachment section.
Click on save and run the flow. It will trigger an email to the users with a pdf version of the report.
Considerations
This action will work only when the report is shared in a premium capacity workspace. If you have a pro license and the workspace is not premium, you might get the below error:
In addition to the above 3 actions, there are many triggers and actions available in Power Automate for Power BI as follows:
Triggers:
Actions
Best Practices for Successful Integration
Data Security and Compliance
Ensure that sensitive data is handled securely, adhering to data protection regulations and internal policies. Use encryption, role-based access controls, and data loss prevention measures to safeguard data integrity.
Test and Monitor Workflows
Thoroughly test all workflows before deployment to identify and fix any issues. Regularly monitor automated processes to ensure they are functioning correctly and making accurate decisions.
Conclusion
The integration between Power BI and Power Automate provides organisations with a powerful toolset to unlock the full potential of their data and automate business processes. By leveraging real-time insights and streamlining workflows, businesses can make informed decisions, improve efficiency, and stay ahead in today's data-driven world. By following best practices and exploring various integration scenarios, organizations can unleash the true potential of Power BI and Power Automate to achieve remarkable outcomes.
Vidi-Corp is a Power BI Consultant and we offer bespoke Power BI training for data-driven businesses. Contact us now to discuss how we can empower you and your team.
