Are you using the Microsoft Power Automate Filter Array Action wrong?

if you need to sift through a large amount of data in your flow ditch the apply to each and condition actions and use the filter array action instead using the apply to each action and condition action is inefficient in this flow example the apply to each action is looping through each item in this list and checking to see if the employee's birth date is today I have a small list of only 14 items if you have a larger list doing it this way would be time consuming the filter array action is a data operation action this action can help you reduce the number of objects in an array by filtering out the data based on your criteria by using the filter array action this flow will first filter out the list of employees based on my criteria I added a condition to check if any items have been returned before continuing on with the flow then I can take the outputs of the filter array action and loop through those items instead instead of looping through 14 items I'm only looping through the items that have been filtered in this video tutorial I'll show you three practical ways to use a filter array action and how to use it properly number number one cross-referencing data if you if you use power automate to import Excel data into a SharePoint list use the filter array action to cross-reference your Excel data with the data that already exists in SharePoint this can also work for cross-referencing one SharePoint list with another to determine which items to move into a list or which items to delete the same goes for cross-referencing files in a document library number two filtering by a key there are a few actions in power automate that don't include a filter query field such as the get all lists and libraries get lists list files in folder get sections in Notebook list channels get tables and many more instead of using an apply to each and condition action to see if the items returned match your criteria use a filter array action instead some of the value keys in these actions aren't even accessible through the dynamic content options the get all lists and libraries action returns all lists and document libraries unfortunately it doesn't include a way to filter the values returned the dynamic content doesn't even list the type key with a filter array action you can filter just the document libraries or lists number three substring matching the odata filter query in power automate doesn't have a contains operator use a filter array action to filter your items based on whether a particular string field contains a specific substring if you have a power automate flow that uses a filter array action and looks like this you are using it wrong keep watching to learn how to use this action properly on my SharePoint site I have this Excel file where the data has been added to my SharePoint list I have a new Excel file of data that needs to be imported this table contains new data as well as the previously imported data I need to filter out the new data that needs to be added to the SharePoint list so I don't end up creating duplicate list items in my Excel table I have a column that stores a custom ID we'll use the values in this column to cross-reference with the items that already exist in our SharePoint list the email addresses are also unique in this case which can also be used if we wanted adjust this flow to suit your data in my flow I'm going to delete the apply to each action while we test and build out the modifications to this flow add a get items action we'll use this action to return the items from the SharePoint list add a select action we'll use this action to pull the values from the email ID column the select action will return an array of values in the from field insert the values Dynamic content from the get items action click this icon to switch to text mode insert the email ID Dynamic content from the get items action here the select action will return an array of values add a join action we'll use this action to convert the array of values into a string don't forget to rename your actions to keep things organized in the from field insert the outputs from the select action above in the join with field insert a comma and a space I'll add a scope action to group these actions together scope action is optional I like to use them in my flows to easily collapse multiple actions so it doesn't take up too much vertical space I'll drag these actions into this scope action next add a filter or reaction we'll use this action to filter out the data returned from the list rows present in a table action in the from field insert the value Dynamic content from the list rows present in a table action in the first value field insert the outputs from the join action change the operator to does not contain I'm using a dynamic table in list rows present in a table action if you want to know how to do this make sure you watch this video the only Dynamic content I have available from the list rows present in a table action is the value and body I'll need to use an expression to access the content returned from that action I'll insert the item function add a question mark square brackets and single quotes I'll need to use the values in the ID column of my Excel table in the outputs of the list rows present in a table action that column is labeled capital I and D I'll enter that in between the single quotes I'll add a compose action before running a test whenever I use a filter array action in my flows I always add a compose action to store the number of items returned from the filter array this helps with troubleshooting my flow it also helps me to confirm whether or not I've set up the filter array action correctly insert an expression use the length function insert the body outputs from the filter reaction above the original Excel file that was imported into SharePoint has 27 rows of data this new file has 50 rows of data I'm expecting the filter array to return 23 items save the flow and give it a test the output of this filter array action has all the items that need to be added to the SharePoint list to help you better understand how the filter array action works I want to break down what is happening in this action the from field requires an array of items in my case it's the values being returned from the list rows present in a table action the first value field contains the IDS from the SharePoint list in a string that looks like this the core function of the filter array action is to determine if the items in the array meet your criteria in this particular case I'm looping through each value specified in the second value field which is the ID as it's looping through each ID it will determine if it exists within the value specified in the first value field in my case the string of SharePoint IDs it's important to note that the order of values entered into the value Fields will differ based on your conditioned argument for example if I use the equals to operator I would insert the column to check in the first value field and insert the value to compare it to in the second value field when a match is found the action will collect that item this is why the output of the filter array action will only contain the items that meet your criteria what if my SharePoint list contains items that don't exist in my Excel table if the SharePoint item doesn't exist in my Excel table I'd like to delete those items from SharePoint first I need to add a select action into this scope action we need to mimic what we did for the SharePoint data earlier we'll use this select action to pull the custom ID from the Excel data I'll insert the value Dynamic content from the list rows present in a table action I'll click on this icon to switch to text mode and insert an expression I'll use the item function add a question mark square brackets and single quotes I'll insert ID in all caps add a join action in the from field insert the outputs from the select action above in the join with field insert a comma and a space add another filter reaction in the from field I'll insert the value Dynamic content from the get items action in the first value field insert the outputs from the join action that contain the IDS from the Excel table change the operator to does not contain in the second value field insert the email ID Dynamic content from the get items action it's important to note that I'm not using the SharePoint ID Dynamic content because that information isn't stored in the Excel table in my Excel sheet I'll delete three rows of data that have already been added to my SharePoint list add a compose action I'll use this compose action to store the number of items returned from the filter array action above insert an expression use the length function insert the body outputs from the filter array action above let's run a test this filter reaction should return three items that need to be deleted from SharePoint the output of this filter reaction has all the items that need to be deleted from the SharePoint list now that our filter array actions are filtering out all the appropriate data we need to add additional actions to this flow I'm going to have both filter array actions running parallel to each other add a parallel branch and drag this filter array and compose action over for the SharePoint items that need to be deleted we'll need to add a delete item action before we do this to prevent this flow from failing add a condition we'll use this condition to check to see if any items need to be deleted I'll insert the outputs from the compose action that contains the number of items returned from this filter array action I'll leave this as is equal to and enter a zero if there aren't any items returned we'll do nothing in the no Branch I'll add the delete item action we'll come back to this action in a minute under this filter array action I'll mimic what I've done on that side in this no Branch I'll add a create item action when you use a filter rate action in your flow you aren't able to access the dynamic content from the filtered array you may be tempted to use the dynamic content from the original action that Returns the items you are filtering such as the list rows present in a table action or the get items action because I don't have access to the list rows present in a table Dynamic content I'll use the get items action to get my point across in order to delete a SharePoint item I need to insert the item id if I select the ID from the get items action you can see that the delete item action is now nested in and apply to each action take a look at the items it's looping through this action is looping through the values returned from the get items action up here not the filtered values which are contained in this filter array action I'll delete the value in ID Dynamic content let's take a look at the outputs from the filter array action all the content can be accessed with an expression and the value key which is the red text between the double quotes in my case I need the SharePoint ID I'll use this text here in my expression first I'll insert the body outputs from the filter array action of the SharePoint items to delete here in the ID field insert an expression we'll use the item function to access the current item being looped through add a question mark square brackets and single quotes I'll insert capital I and D to return the SharePoint Item ID I'll add and apply to each action in this no branch and pull the create item action into it add the body outputs from the filter rate action of Excel data to add here I'll use the same expression for each field I want to import in SharePoint I'll delete the file I just added let's give this a test let's check the SharePoint list it's added 23 items to my SharePoint list and has removed three I have a total of 47 items don't forget to turn on the concurrency control in your reply to each actions to optimize your flow if you are setting variables within your reply to each action leave the concurrency control off in this flow I have a get all lists in libraries action the outputs of this action list three keys name display name and type lists have a type of 100 and document libraries have a type of 101. even with an apply to each and condition action you can't filter out by type these are the only Dynamic properties available to select from delete the apply to each action and add a filter array action in the from field insert the value Dynamic content from the get all lists and libraries action in the first value field we're going to add an expression use the item function add a question mark square brackets and single quotes enter type with a capital T in between the single quotes to match the key from the outputs leave the operator as is equal to I want to return just the document libraries sometimes the filter array action doesn't work the way you think it should let me show you I'll enter a 101 into the second value field and run a test no results are returned the type is stored as a string and not an integer the condition argument in the filter array action looks okay but if we click on edit in advance mode you can see that the number isn't wrapped in single quotes this means it's actually passing an integer value we need to edit this expression so single quotes wrap the number don't try to type in this editor as your cursor will jump around instead copy this expression to your clipboard and paste it into a text editor ensure that there is a single quote on either side of the number copy the expression to your clipboard and paste it back into the action lastly add a compose action to store the number of items returned from the filter array action use the length function and insert the body outputs from the filter array action above Let's test it out again now it's returning all the document libraries you can apply the same concept to filter out different actions by using the key from the outputs of the action the advanced mode in the filter array action will also allow you to filter arrays with multiple conditions I'll cover this near the end of the video or skip ahead with a timestamp listed in the description box below whenever you use a filter or reaction it's always a good idea to add a condition action to check if any items have been returned because I've used a compose action above to store the number of items returned I'll insert that into my condition action I'll keep the operator as is equal to and enter a zero if no items are returned from the filter reaction I'll do nothing if items are returned I'll insert actions into the no Branch to Loop through items returned from the filter array action insert the body outputs from the filter array action into and apply to each action use compose actions to store the values that you need for the rest of the actions in your flow I would recommend running tests to confirm the outputs of these compose actions before adding any additional actions to your flow this will help to speed up your flow building as compose actions take very little time to run for this example I'll add two compose actions one to store the name and the other to store the display name of the document Library being looped through those outputs for the filter array action are actually available to select from the dynamic content list usually the only actions available are the body and item if that's the case you will need to use an expression to access the dynamic content from your filter array action as we did in the first example I'll add another compose action to show you what that looks like use the item function add a question mark square brackets and single quotes insert the key from the filter array outputs to access the dynamic content in between the single quotes in this example I'll enter display name with a capital d and n as it appears in the filter array output run a test as you can see both these compose actions have the exact same output the actions that include a filter query field such as the get items and get file properties only action can't filter items and files with the contains operator let me show you in the clients document library of my SharePoint site there are meeting minutes in each of these client folders in this custom view I can filter out all the meeting minute files I want to return a list of meeting minutes from this month only the file name syntax makes this easy as I can filter out the meeting minute files that contains meeting minutes underscore 2023 OA in the file name in my power automate flow if I try to use a contains operator in the filter query field the flow fails because it's an invalid expression I'll use a filter or reaction instead in the from field insert the value Dynamic content from the action above in the first value field I'll select the name Dynamic content change the operator to contains in the second value field I'll insert the string of text I'd like to search for lastly add a compose action to store the number of items returned from the filter array action use the length function and insert the body outputs from the filter array action above Let's test it out you can make this flow Dynamic by using a dynamic date instead a lot of compose action above the filter or reaction I'll enter the meeting minutes in underscore and I'll insert an expression we'll use the UTC Now function to get the current date and time wrap that with a format date time function place your cursor inside the closing bracket and add a comma and single quotes enter your date format in the single quotes I'll delete this text and replace it with the outputs from the compose action above run another test it's important to note that if you are comparing strings of text the cases must match I'll change these M's to lowercase and run another test this test didn't return any results to avoid any case sensitivity issues wrap the dynamic content in a two lower function this will convert all the text to lowercase click on edit in advanced mode I'll copy this expression to my clipboard and paste it into a text editor I'll wrap the dynamic content in a to lower function I'll copy this text and paste it back into the filter reaction let's run another test the condition action is user friendly and intuitive to use however did you know that the condition action has a limit of 10 conditions also it's inefficient to Loop through the entire list and run a condition check on every item the filter reaction can be a bit deceiving as it looks like it can only handle one condition with the advanced editor you can use an expression to filter by multiple conditions for this demo I want to filter out the employees who have manager in their title and their birthday is in seven days to help you better understand the expression start in the basic mode I already have a compose action that contains a dynamic date which is 7 days from now in the filter or reaction I'll insert the value Dynamic content from the get items action in the first value field I'll insert the birthdate Dynamic content I'll change the operator to contains and I'll insert the outputs from the compose action that contains my Dynamic date click on edit in advance mode I'll copy this to my clipboard and paste it into a text editor I'll switch back to basic mode and compose my next condition in the first value field I'll insert the job title Dynamic content I'll leave the operator as contains and I'll enter the word manager in lowercase I'll convert the dynamic content to lowercase in a minute click on edit in advance mode and copy the expression to your clipboard and paste it into the text editor first I'll remove the at symbol from both of these expressions add a comma to separate the conditions next I'll wrap the job Dynamic content in a to lower function to convert the job title to lowercase place your cursor at the start of the expression and add back one at symbol wrap the entire expression with the and function for an or condition wrap your entire expression in an or function I'll copy this expression to my clipboard and paste it back into the filter array action don't forget to add a compose action to store the number of items returned from the filter reaction let's run a test what other power automate actions would you like me to cover in more detail let me know in the comments down below if this video helped you better understand how to use the filter array action please consider giving it a like don't forget to subscribe so you don't miss out on any other power automate action tips and tricks thanks for watching

Channel: A Creative Opinion

Views: 6,948

Keywords:

Id: HVAf1aTEQNg

Length: 22min 44sec (1364 seconds)

Published: Mon Aug 21 2023