Empowering Users with Timely Data Updates in Power BI Using Power Automate and ADF
Has there ever been a situation in your project where data was successfully loaded into Power BI, but business users remained unaware of its availability?
To address this challenge, there is a method we can employ to ensure that our business users are notified when the most recent data has been loaded into Power BI, enabling them to access reports with the latest information.
We can use Microsoft Power Automate which is available by default in all Office 365 applications to keep our business users updated about the latest data loaded into Power BI. Power Automate boasts a library of over 150 pre-built connectors. These connectors unlock various automation possibilities, including sending messages to Teams channels or groups, triggering email notifications, and much more.
This blog dives deep into creating a Power Automate flow designed to send messages to Microsoft Teams channels or groups.
We’ll explore how to connect the Power Automate flow seamlessly from an Azure Data Factory (ADF) pipeline to send messages to the Microsoft Teams channel or group.
Follow the below steps to create a powerful automated flow to send messages to Microsoft Teams
1. To begin building your Power Automate flow, navigate to Microsoft 365 and access Power Automate.
Once you are in Power Automate, From the left-hand side menu, locate and select “My flows”. Then, choose “Instant cloud flow”
2. select the “When an HTTP request is received” option in this window and click on Create.
3. Once you are in the flow, give the name for the flow, you can turn off the new designer (which is a new UI for creating the flow) and select “Anyone” for who can trigger the flow and the URL will be generated for the flow after saving the flow.
4. After making the above changes, click on “Use sample payload to generate schema” give the schema in JSON, and click on Done.
Note: We are not passing any values to the parameters here, as we will pass the values from ADF.
5. After giving the schema, then click on “New Step” and search for teams and select teams, and in Actions, select “Post message in a chat or channel”:
6. Get the group_id and channel_id for the Microsoft Teams channel to which you want to send messages.
You can go to Microsoft Teams and go to the channel and under the drop-down menu, select “Get link to the team” and from that link, you can get the channel_id and group_id.
Below is the example to get channel_id and group_id:
From the channel link, you can follow the below measures to get channel_id and group_id.
channel_id:19%3aYohg7O5Vj1h39FteHe2MxHGEFM9Hjib7juHn3ysPOr4I1%40thread.tacv2
group_id: 0eebk6gjbk-75Hf-70Yh-9665–5e4e22ae43c0
7. After selecting “Post message in a chat or channel”, you can give below options:
Post as: Flow bot
Post in: channel
Team: Give the group_id from the dynamic content that we created earlier.
Channel: Before giving channel_id, you need to convert ‘%3a’ to ‘:’ and convert ‘%40’ to ‘@’ to send messages in a particular channel. This change can be done in the power automation flow.
Message: You can write the message you wish to send in a channel or group.
Note: For group_id, select dynamic content and select group_id.
For channel_id, as you have to convert or replace ‘%3a’ to ‘:’ and convert ‘%40’ to ‘@’, go into expression and give the below expression. (This is only for channels if you select other options such as Group chat or chat with Flow bot in “Post in”, there will be different options.)
8. Now, again click on “New Step” and select HTTP action.
Then, give POST as the Method, and in URI, give the callBackURI variable which we created earlier from the dynamic content.
We are performing the above HTTP action to inform ADF that it has received the request successfully from ADF and then our ADF pipeline will be successfully completed.
9. The last step is, once you click on save, a URL will be generated for our flow, you can get the URL from the “When an HTTP request is received” action.
Now, you have created a power automate flow to send messages in a channel or group and our final Power Automate flow looks like below:
You can call the Power Automate URL via ADF or the Postman application too.
Below Steps need to be performed in ADF to connect ADF to Power Automate
Now, we will look at how we can call the power automate flow from ADF.
1. Go to the ADF pipeline create a new pipeline and select webhook activity. And then in parameter, create 3 parameters to send them to the power automate flow.
Parameters are power_automate_url, group_id and channel_id
Note: Parameter names in ADF should be the same as parameter names created in the power automate flow.
2. Inside webhook activity, give these parameters in the following way
Inside the body, pass the parameters of group_id and channel_id in the following JSON structure.
That’s it, our ADF pipeline is ready to call the power to automate flow.
You can test it by debugging the pipeline or calling the pipeline from Airflow. You will receive the message in the intended channel or group.
Conclusion
By leveraging Microsoft Power Automate alongside Azure Data Factory (ADF), we can seamlessly bridge the gap between data loading in Power BI and user awareness. Through the detailed steps outlined in this blog, you can create a Power Automate flow tailored to notify business users of the latest data updates in Power BI via Microsoft Teams channels or groups. With this integration, users can stay informed and access reports with confidence, ensuring data-driven decision-making across your organization.
Author: Vinay Boddu