Filter array with 2 excel tables

[PC_Animal]

HI,

I've got two excel files with data in each one.  I use the get rows to return the tables.

In table 1 I data which all has a unique ID.  This might also be present in table 2.  

Can I use a filter array to give me only the rows which exist in both tables.  Something like a union?  I want to then iterate through the results.  Can this be done easily?  

[creativeopinion]

@PC_Animal You might find this YT Tutorial helpful: Are you using the Microsoft Power Automate Filter Array Action wrong?

In the first section of this tutorial I cover how to cross-reference an Excel table with a SP list. Although you are looking to cross-reference an excel table against another excel table—the same concept can be applied. You'll want to use the contains operator in the Filter Array action. 

In this video tutorial I’ll show you 3 practical ways to use the Filter Array action and how to use it properly.

1️⃣ Cross-Referencing Data

2️⃣ Filtering by Key

3️⃣ Substring Matching

Did you know that the Condition action has a limit of 10 conditions? Although it might look like the Filter Array action can only accept one condition—this is not true. By using the advanced mode you can enter multiple conditions into a Filter Array action with an expression.

IN THIS VIDEO:

✅ 3 Ways to Use the Filter Array Action

✅ How to use the Scope Action to Group Actions

✅ How to Check the Number of Items returned from a Filter Array Action

✅ How to Cross-Reference Data in Excel with a SharePoint List

✅ How the Filter Array Action Works

✅ How to Access the Dynamic Content from a Filter Array Action

✅ How to Filter Items by a Key

✅ How to Filter Items by Matching a Substring

✅ How to Use Multiple Conditions in a Filter Array Action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[Nived_Nambiar]

Hi @PC_Animal 

is it something simmilar you are looking for ?

Table 1

Table 2

Now see the below

1. 

2. 

3. 

4. 

5. use compose action with intersection to find common rows 

Expression used- 

intersection(body('Select_TABLE_1'),body('Select_Table_2'))

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[PC_Animal]

Hi,

i don't follow this.  

I list the rows from the two tables.  I then create a filter array.  However that then creates a apply to each loop.  

Am I doing something wrong?

[creativeopinion]

@PC_Animal 

Task List

Task List – Cross Reference

It's not clear what you want to do with the items that exist in both. I'm going to assume you want to update one table with info from another. In my example I have two tables:

• Task List – 22 Rows
• Task List – Cross Reference – 7 Rows

All 7 rows in the Task List – Cross Reference Table exist in the Task List Table. 

---

List Rows Present in Table

In the root of your flow you need to add two List Rows Present in a Table actions. Tip: Rename your actions to keep your flow organized. This will also help you to easily identify which action is pulling data from which table when they are collapsed.

Additional Tip: Use the filter query action to reduce the number of rows returned (if you don't need to return all rows and can filter by a single column—do that so it helps your flow to run more efficiently). My tables don't have many rows so I wont use a filter query. 

Get the ID's from the Table You are Cross-Referencing

I want to check if the rows in the Task List Cross Reference table exist in the Task List Table. I've added a Select action to pull out the IDs from that table. 

In the From value field insert the value dynamic content from the List Rows present in a table action. Click on the icon to switch to text mode.

In the Map field, insert the dynamic content from the List Rows present in a table action that is storing your unique identifier. For my table it's key. 

Add a Join action. Insert the output from the Select action into the from field and in the join with field enter a comma and a space. You could also enter a pipe or semi-colon. Up to you.

Run a test. Review the output of the join action. It should output all the unique IDs from your selected table values into a string separated by a comma (or whatever separator you used in your flow)

Filter Array – Cross Reference

Add a Filter Array action. Insert the Value dynamic content from the List Rows Present in a Table action you that has your cross-reference values. This is why it's important to rename your actions so you select the correct dynamic content.

In the first value field insert the output from the Join action. 

Change the operator to contains and insert the dynamic content of the unique identifier from the correct List Rows Present in a table action. 

Depending on how your ID's are set up you may want to include the separator a the end. I'm doing this because my ID's start at 1 and this will cause 

Return a Count of Items

Insert a Compose action. Add an Expression. Use the length() function.

Select the Dynamic content tab and insert the body dynamic content from the Filter Array action into the length() function.

For testing purposes I will adjust the keys in my cross-reference list where the last three keys aren't included in my main table. 

When I run another test, only 4 items are filtered (aka only 4 items from the Task List Cross Reference table were found in the main table)

Condition Check

Add a Condition action to your flow. If items have returned (aka there is number stored in the Compose action—that is not equal to 0), add the rest of your actions to the Yes branch. If not, do nothing.

Loop through Filtered Items

Add an Apply to Each action to the Yes branch. You'll want to loop through the filtered items. 

However, you'll notice that the only dynamic content available from the Filter Array action is Item and Body. Refer to this section of a YT Tutorial I uploaded on how to get dynamic content from a Filter Array action

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉 Level up your Power Automate skills by checking out my tutorials on YouTube
👉 Tips and Tricks on TikTok and Instagram

[WillPage]

You could try and follow this article on my blog: https://willpage.dev/2022/06/07/comparing-two-arrays-in-power-automate-and-logic-apps/

[Nived_Nambiar]

Hi @PC_Animal 

Did u tried the approach which I have shared ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌