Need guidance on Excel, Power Bi, Automation and Sending email

Question

I am more familiar with Power Automate than Power Bi. I have done a couple of project using Power Bi. I am sure I have no concept of its full capabilities. Here is my situation...

I utitilize a large Excel doc which i have to create relationships to get the data to talk to each sheet. What I have been asked to do is create an email to the client when their case goes from Open to Closed. It is a questionnaire to ask about their service. It already has rows into the thousands. I used this forum entry: How do I send a custom email to each email in a column. It has me on the right track. What I do not want to happen is an individual gets a duplicate email each time the flow is ran. I am unable to delete the data. What I was thinking about is if once the case is closed, an email is sent and the row with the information is moved to another sheet or something of which could be distingiushed after the initial run.

Any suggestions?

alexisolson · Answer

How about adding a 0/1 column that you check before sending an email?
&nbsp;
Initialize the column to all 0 but whenever you send an email, update it to 1. Set a rule in your automation that only rows with 0 get sent.

jaideepnema · Answer

Hi&nbsp;aboynamedsue&nbsp;,In order to make it work there are certain points:

Your Dataset should reside on Office 365(Excel Online).
You have to add a Flag(Y/N) which denotes whether the closure email has been sent to the user after the case got closed.
And the flow whenever triggered should update this Flag to Y whenever the email is sent.
At last the report dataset should be refreshed.

&nbsp;
So just to recreate your scenario i have used the following dataset:
Table containing the case information.

&nbsp;
Table containing member information

&nbsp;
These two tables are joined basis member id.
&nbsp;
Now create a flow like this:

&nbsp;
When you will trigger this flow you will get an email like this:

&nbsp;
Hope this helps !!
&nbsp;
Please accept this as a solution if your question has been answered !!
Appreciate a Kudos&nbsp;😀
Connect with me on LinkedIn: https://www.linkedin.com/in/jaideepnema/
&nbsp;