Embedding a Power Automate button within a Power BI report enables the automated generation and distribution of emails using dynamic information directly from the report. However, one must manually open the Power BI report and click the embedded Power Automate button to trigger the email process.
The question is – is it possible to automatically send emails from a Power BI report without any manual intervention? For example, this could be useful if users want monthly emails to be sent automatically when certain conditions are met.
The answer is – Yes!
Use Case Example
Consider a Power BI report that displays a list of projects with attributes such as the project name, project manager, due date, and status. The report also includes a measure indicating whether a project is overdue (i.e., past its end date). Originally, an embedded Power Automate button could be used to send emails to project managers when their projects are overdue, requesting that they take action. However, rather than relying on a user to open the report and initiate the emailing process, this project provides a solution for automatically sending these notifications on a monthly basis—no manual intervention required.
Applications Used
- Power BI Paginated Report
- Power Automate
- Outlook
This project starts with a Power BI Paginated Report already completed with the fields below (note – creation of a Paginated Report isn’t covered here):
Below is an overall summary of the Power Automate flow:
Step 1: Trigger flow based on “Recurrence” trigger (manual button shown above due to testing only)
Complete applicable parameters.
Step 2: Export data from paginated report
Identify the applicable workspace and report. Select CSV as the output.
Step 3: Convert the CSV to an array
These are the key steps that convert the CSV file to an array. Note that in the summary picture on Page 2, these steps are encased in a “Scope” control as follows. This is optional but makes it easier to organize.
The main challenge is obtaining the information from the output of the “Export Data” step. The output is in one long text string where each new row is separated by the “\r\n” text. An example of an output will look something like this:
The output can be separated into individual rows using the split() function. This function requires both the text to split, but also a delimiter. The highlighted “\r\n” is the delimiter between individual rows. Thus, the split function requires “\r\n” to be used as the delimiter. However, in order to do this, the UniComponent() function must be used. The split() function will not recognize the text “\r\n”, even though in the CSV file it appears as normal text.
The following two actions will address this. The first creates a variable the mimics the “\r\n” text using the Uri Component values:
The CSV may also need to be cleaned up to remove the first row, which will contain the column names, and then any trailing blank rows. In my situation, the last two rows were blank and needed to be removed. The following actions address that.
The skip() function skips the number of rows indicated, in this example 1.
The take() function is then used by indicating how many rows that you want. In this example, the last two rows my array were blank so I needed to “take” the # of rows in the total CSV value less 2.
The result of these steps should leave you with an array of values similar in the format as shown below. Each row represents an individual row in from the paginated report.
Step 4: Loop through each record and send automated email
This final step is where the applicable data is extracted from the CSV file and put into an email. All of these actions are enclosed in an “Apply to each” action as shown in the image.
Let’s walk through the first two steps as these pull the first row of the CSV and then split the data by the comma delimiter.
This next area is optional, but using the “Compose” action to develop individual outputs for the specific elements you want to include in your email makes things easier. In the example below, I am creating 4 separate Compose actions to pull the Project Name, Project Manager, End Date, and Email. This can achieved by using the outputs() function and identifying the index of the record.
Note: This uses zero-based indexing so the first record will be indicated with a [0].
You are now ready to use email action step. The image provides two different examples of using both the Compose outputs for each category above, versus skipping that step. See the “Title” and “Comments” sections as those are created using the same process as above.
A final summary of the steps is as follows:
That sums up the approach that I took.