How to use Power Automate to email Power BI content in email based on filter condition

[POSPOS]

Hi All,

I have a Power BI report with one table as below:

Employee ID	Department
Emp A	Dep 1
Emp B	Dep 2
Emp C	Dep 3
Emp D
Emp E

My requirement is to create a flow in Power Automate to email a user only when the departments are empty. If departments are not empty, then email should not be triggered.

In this example, an email should go out with the below table in the email body.

Employee ID	Department
Emp D
Emp E

The filter condition to be applied in the Power Automate flow is Department = NULL

Can someone please advise on how to achieve this?

Thank you.

[Relay9]

Hi @POSPOS 

To achieve this in Power Automate, follow these steps:

Step 1: Create a Scheduled or Triggered Flow
1. Go to Power Automate and create a new flow.
2. Choose a trigger, such as “Recurrence” (for scheduled runs) or “When a Power BI data refresh completes” (if Power BI is the trigger).

Step 2: Get Data from Power BI
1. Use the “Run a query against a dataset” action.
2. Select your Power BI workspace, dataset, and table.
3. Write a query (DAX or OData) to filter out employees where the department is NULL.
Example DAX Query:

EVALUATE FILTER(Employees, ISBLANK(Employees[Department]))

Step 3: Condition to Check for Empty Departments
1. Use the “Filter array” action.
2. Select the Power BI dataset output.
3. Set the condition:
• Department is equal to null (or empty).

Step 4: Send an Email with the Filtered Data
1. Use the “Send an email (V2)” action.
2. Set the recipient, subject, and body.
3. Format the email body to display the filtered employees in a table.
Example email body (HTML table format):

<table border="1">
<tr><th>Employee ID</th><th>Department</th></tr>
<tr><td>Emp D</td><td></td></tr>
<tr><td>Emp E</td><td></td></tr>
</table>

Step 5: Test and Deploy
1. Run the flow manually or wait for the trigger.
2. Ensure that an email is sent only if there are employees without a department.

This setup ensures Power Automate only sends an email when there are employees with empty departments. Let me know if you need more details!

[Anonymous]

Thanks for the reply from Idrissshatila.

Hi @POSPOS ,

Please refer to the following steps:

1. Create a table and filter out data with empty departments:

2. Copy the query for this visual under the Performance analyzer panel.

3. Select a trigger according to your needs, then add a new action: Run a query against a dataset. Select the corresponding workspace and dataset, and then paste the DAX code you just copied.

4. Use Create HTML table to include in the body of the email and choose First table rows.

5. Add the final step: Send an email (V2) and the body uses the output of the HTML table just created.

All the steps are shown in the picture:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[POSPOS]

@Anonymous  - Thank you for the detailed steps. 

My requirement is to send an email only if there are blank departments. If no blank departments, then email should not be triggered.

[Anonymous · Accepted solution]

Hi @POSPOS ，

In your flow, add a Condition action:

Next, set the condition. When the number of table rows generated by the Create_HTML_table operation is greater than 0, the condition evaluates to true, and the email will be sent:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Idrissshatila]

Hello @POSPOS ,

check if this works for your situation https://youtu.be/AroolNHeY8o?si=_XDxUmQPswPdUfJe

[POSPOS]

@Idrissshatila  - This does not allow to add report output in the email body. My requirement is to inlcude email output as well.