Filter array is returning incorrect results

[allan_t]

I don't know why I can't get the filter array function to work correctly.   My research on this topic hasn't helped me thus far.  Any help the community can provide would be greatly appreciated.   

Flow objective:  Update SharePoint list with the latest information on specific projects contained in an Excel file.  The Excel file contains rows with projects that need updated information.  SharePoint list contains all projects.  The key between Excel and SharePoint is a text column containing a project identifier (known as WorkID, eg 0054321). 

Array1 is from List rows present in an Excel table.  It contains a text column with a project identifier with the format of 0054321.  I used Select to create this array.  The project identifier value is stored in the Excel column 'Work ID _x0023_'.  

Sample of Array1 output:

Array2 is from Get items from SharePoint list, SP list contains many columns. It contains the project identifier text column (column name 'PlanviewWorkID', the list item ID (column 'ID') and several other columns.  I used Select to create an array of the project identifiers from the SharePoint list.  

Sample of Array2 output:

Array3, I used a Compose action with intersection expression, to find the project identifiers that were present in both arrays.  I've validated that this intersection array output is correct and only contains project identifiers that were present in both Array1 and Array2.  

Sample of compose intersection output for Array3: 

Here's the point in the flow where I'm struggling to get correct output.  The Array3 containing work IDs that intersected need an update in the SharePoint list.  To make an update to the SharePoint list item, I need the list item ID value from the Get Items array output.  I used Select, to create an array4 from Get Items output that contains both the SharePoint item ID and the project identifier.  

Sample output of array4: 

I've tried filtering array4 so it only contains "WorkID"s that are also present in the intersection array3.  With this filtered array4, I could use the ID value to perform the Update Item connector and update the SharePoint list item.  The filter array output is null, so I'm not using correct filter criteria.  Any insight on what I've done wrong?  

Edit, PS. 

My research so far included blogs and videos from @DamoBird365 and @creativeopinion whom have helped me immensely in the past.    

Intersection function Efficient Union, Except And Intersect – Great Method - DamoBird365

Comparing arrays video https://youtu.be/4IphRdADJBc?si=iG9T9MP0aMGhgqK5

Comparing arrays blog Comparing two arrays without an apply to each? (pnp.github.io)

Filter array action https://youtu.be/HVAf1aTEQNg?si=2rQK7Pu9Qc8j1ivX

[abm]

Hi @allan_t 

The filter array you are comparing against a particular item with another array.

Above item()?['WorkID'] is a string value comparing against outputs('Compose_List_of_intersect_WorkID') which is an array.

What you need to do is Add a loop above the Filter array then map the outputs('Compose_List_of_intersect_WorkID') then inside the array use the above filter array action step. Remove the right hand side mapping outputs('Compose_List_of_intersect_WorkID')  and change it to expression item() which is each item of outputs('Compose_List_of_intersect_WorkID') you can compare with.

Thanks

[allan_t]

Thanks for advice @abm .  I'm new to using Select and Filter connectors.  I'm not sure how to implement the suggestion you've provided.   

re: Add a loop above the Filter array then map the outputs('Compose_List_of_intersect_WorkID') then inside the array use the above filter array action step.

Do you mean put the Filter array connector inside an Apply to each loop? How do I do that?  

re: Remove the right hand side mapping outputs('Compose_List_of_intersect_WorkID')  and change it to expression item() which is each item of outputs('Compose_List_of_intersect_WorkID') you can compare with.

I've revised the right hand side to item().  I'm getting null output I assume because I don't know how to interpret the earlier part of suggestion yet.  

[creativeopinion · Accepted solution]

@allan_t In my YT Tutorial: Are you using the Microsoft Power Automate Filter Array Action wrong? I cover how to cross-reference an Excel table against a SP list. Unfortunately in this tutorial, I don't cover how to Update an existing item. I only cover how to Add a SP item (if it doesn't already exist) or how to delete an item (if it exists in SharePoint but doesn't exist in the Excel table).

In this section of the tutorial I cover how to Add an Excel Item that doesn't already exist. However, instead of using the does not contain operator, use the contains operator. Follow the tutorial from the beginning but make that small change. This way the Filter Array action will filter the items that already exist and need to be updated.

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

[abm]

Hi @allan_t 

Add the For Each Loop then drag and drop the filter array (Your current step inside the loop). Then change the expression to item(). This will provide the exact match. The contains you could use it but the risk is its not the exact match.

Thanks

[allan_t]

Hi @abm , just when I think I understand, my test either fails or I get a null output from the filter array.  Are you able to pinpoint what I'm doing wrong?  Here's the structure and syntax I've used. 

[abm · Accepted solution]

Hi @allan_t 

This is what I meant by

Above Filter Array expressions for the comparisons are

item()?['WorkId']

item()

Basically we are iterating each element from the first compose output in the Apply To Each loop. Then using the filter array to check the exact match.

I know you accepted the solution but for the future reference it might be helpful.

Thanks

Thanks