Answered

Power Automate to Filter Excel Columns Based on Multiple Conditions

(0) Share

Report

Posted on by coolbeans23

Hello,

I'm a newbie to Power Automate and need some help. I would like Power Automate to filter an Excel file saved on SharePoint, extract only the unique records (in this case based on the Owner column , which is a unique identifier) where each Car1, Car2, Car3, and Car4 is a Fail, get the email address associated with those records and send each Owner an email along with a HTML table of the records where the Car is a Fail. The Action column helps identify the rows that have a Fail/Pass. I'm only interested in the rows where Action column is Correction Needed.

Owner	Car1	Car2	Car3	Car4	Action	Email
5	Pass	Fail	Fail	Fail	Correction Needed	Cam@hotmail.com
5	Pass	Pass	Pass	Pass	None	Cam@hotmail.com
5	Pass	Pass	Fail	Pass	Correction Needed	Cam@hotmail.com
2	Fail	Pass	Fail	Pass	Correction Needed	Bob@hotmail.com
2	Pass	Pass	Fail	Pass	Correction Needed	Bob@hotmail.com
3	Pass	Pass	Pass	Pass	None	Mike@hotmail.com
7	Pass	Pass	Pass	Fail	Correction Needed	Tom@hotmail.com
9	Pass	Fail	Pass	Fail	Correction Needed	Mary@hotmail.com
9	Fail	Pass	Pass	Pass	Correction Needed	Mary@hotmail.com

For example, Owner 5 a correction is needed (based on Action column) for rows 1 and 3 only. So owner 5 will receive an email that shows a HTML table of those two records. On the other hand, Owner 3 should be the only owner not to receive an email because the Action is none - all the cars has a pass for it.

Please help me build a example flow from this! Thank you in advance.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

v-dezhili-msft Microsoft Employee on at

Like (1)

Report

Hi @coolbeans23 ,

I made a test for your reference:

Best Regards,
Dezhi

Was this reply helpful? Yes No
Verified answer

coolbeans23 53 on at

Like (0)

Report

Awesome! I made a slight variation from your flow. I did not utilize two Filter Arrays, just one because I made an initial filter using the Odata Filter Query under advanced option in the second step. This I read helps with speed performance for the subsequent steps. Worked great!

I do have a question for understanding purposes: In the first Filter Array why do you check if the table values equal to Owner? Is it comparing to a distinct list of Owner from the Compose step? Also, in the last step, in the "To" part of the email, is the expression referencing the distinct emails you generated in the Compose step?

Was this reply helpful? Yes No
coolbeans23 53 on at

Like (0)

Report

Is it possible to modify the flow so that I'm given an option to select All or a unique list of Owners before sending out an email? I want to have the ability to send a mass email or select from one or multiple individual owners before sending the email. I'm thinking radio buttons or a user input would be best for making those selections. The default should be "All" if no individual Owner is selected. Is this something that is do-able or should I create another column/table in Excel that checks for this selection?

Was this reply helpful? Yes No