This is almost exactly what I need, but a lot of super helpful info here. Let's say my array has multiple objects and I want to filter out vs another array with multiple objects, without doing an apply to each. For example - I have table 1 with thousands of line items, columns A, B and C. I have table 2 with a few hundred line items with matching columns A, B and C. I want to exclude line items in file 1 that are matching records in file 2, where the data in columns, A/B/C all match for that 1 line item. For example, File 1: Column A Name: Jon, Column B Occupation: Retail, Column C Salary: 10k File 2: Column A Name: Jon, Column B Occupation: Retail, Column C Salary: 10k If i find this line item in both files, i don't want this to appear in my output array. You made it work for single item comparison, but I'm trying to compare 3 items without kicking into an "Apply to Each" loop.
Do you have any really unique value in your lists, like an email address, or an employee id? Because I was also struggleing to find a way to compare more complicated objects of two arrays. But I might have a crazy idea.
@@MarkusSchiller Column A is a series of alphanumerical characters. Column B is 1 of 3 dropdown options and column C is another series of alphanumerical characters totally unique from Column A. So the data between column A/B/C is completely unique. So I'm trying to compare unique line items between 2 files. Data in column A might match in both files, that's good, i want that to pass through. Data in columns A and C might match, which is good. But if the data in column A, B and C all match, I don't want that object to pass through the filtered array. I'm open to crazy ideas, it just might take a bit to learn if there's some heavy coding involved. lol.
@@jonsandler2878 If you really just need the non matching items from your file 1, then I've solved it with just two actions. Which is also good for me, because I can use this in one of my own projects. Now I just need to find the time to make a video for it, because it might be a bit confusing to just write it down.
@@MarkusSchiller If you want to PM me the instructions and maybe a couple screenshots, that might work for me in the interim? I'm not completely illiterate with power automate, so we can give that a shot and see if it works :)
@@jonsandler2878 basically, you'll need to use the select action on your second array and the output should be a concate() of your properties, that you want to compare. The second action will be a filter of your first array, where you use the output of the select action and use the "does not contain" option and the value to test it against needs to be the exact concate() of the same properties in the same order. So you simplify the objects to strings and compare strings against each other.
This is exactly what I'm looking for but still unable to achieve correct results. We have 93 vehicles in our fleet. Every week, each vehicle should be inspected. I'm trying to identify vehicles that have not been inspected based on (3) arrays. varVehListNos is an array that is a listing of all vehicles by VehNo. varVehInspNos is an array that is a listing VehNo that have an inspection from the week. varVehInspMissNos should be an array of VehNo from varVehListNos that are not present in varVehInspNos. Any suggestions would be appreciated.
Hi! Are your arrays "varVehListNos" and "varVehInspNos" simple arrays, or do they contain objects. I've made a very simple flow to demonstrate it with simple arrays: Trigger is "Manually trigger a flow" First Action: initialize array variable "varVehListNos" with values: [ "Car01", "Car02", "Car03", "Car04", "Car05", "Car06", "Car07", "Car08", "Car09", "Car10" ] Second Actions: initialize array variable "varVehInspNos" with values: [ "Car02", "Car04", "Car06", "Car08", "Car10" ] Comment: So every second car has been inspected and cars 1,3,5,7 and 9 should be the ones that haven't been inspected. Third Action: Filter array: "From"-Value: varVehListNos Comment: In your case that array should have all the cars available anyway Filter - click on "Edit in advanced mode" Paste this in: @or( not(contains(variables('varVehListNos'), item())), not(contains(variables('varVehInspNos'), item())) ) Test it and the output should be: [ "Car01", "Car03", "Car05", "Car07", "Car09" ]
Hi! Yes and no. There is a workaround that I've come up with and I've started to make a video about it, but I haven't finished it. It's basically turning the properties of the objects of your two arrays into a single string and make an array out of that and then compare it. The tricky part is, to select the object properties that you want to compare and create the identical structure, because otherwise the comparison would not work.
Hi, could you please advise, how do I use the contains, if my arrays have more than 1 column. Say,i just want to look at column 1 (e.g. Last name) of both arrays, even though their column 2 are totally unrelated.
Hi, the problem is that you will need to simplify one of your arrays. Let's say you want to filter array1 and compare it with the values of array2. Then use a "Select" action on array2, to get a one-dimensional array of all your LastNames of array2. In the "Filter array" action, you will filter your array1, then use the output of the "Select" action in the first field, compare it with "contains" and in the last field you will enter "item()?['LastName']". That will give you back all the items of array1, where the LastName is also in array2
You'll need to provide some more details why these are not working for you. I'm using these methods in a few of my projects that also include arrays from SharePoint lists/libraries or other sources that provide more complex arrays consisting of objects. You'll have to break them down to more simple arrays and extract the information with "Filter array" or "Select".
note sure why this is very low views. This is splendid technique indeed. Thank you for making my day.
Thank you, maybe not a lot of people face that problem, or they found a solution elsewhere 🙂
Thank you !!! i have spent 6 hours looking for a solution :D
I'm happy I could help! :-)
This is almost exactly what I need, but a lot of super helpful info here. Let's say my array has multiple objects and I want to filter out vs another array with multiple objects, without doing an apply to each.
For example - I have table 1 with thousands of line items, columns A, B and C.
I have table 2 with a few hundred line items with matching columns A, B and C.
I want to exclude line items in file 1 that are matching records in file 2, where the data in columns, A/B/C all match for that 1 line item.
For example,
File 1: Column A Name: Jon, Column B Occupation: Retail, Column C Salary: 10k
File 2: Column A Name: Jon, Column B Occupation: Retail, Column C Salary: 10k
If i find this line item in both files, i don't want this to appear in my output array.
You made it work for single item comparison, but I'm trying to compare 3 items without kicking into an "Apply to Each" loop.
Do you have any really unique value in your lists, like an email address, or an employee id? Because I was also struggleing to find a way to compare more complicated objects of two arrays. But I might have a crazy idea.
@@MarkusSchiller
Column A is a series of alphanumerical characters. Column B is 1 of 3 dropdown options and column C is another series of alphanumerical characters totally unique from Column A.
So the data between column A/B/C is completely unique.
So I'm trying to compare unique line items between 2 files. Data in column A might match in both files, that's good, i want that to pass through. Data in columns A and C might match, which is good.
But if the data in column A, B and C all match, I don't want that object to pass through the filtered array.
I'm open to crazy ideas, it just might take a bit to learn if there's some heavy coding involved. lol.
@@jonsandler2878 If you really just need the non matching items from your file 1, then I've solved it with just two actions. Which is also good for me, because I can use this in one of my own projects.
Now I just need to find the time to make a video for it, because it might be a bit confusing to just write it down.
@@MarkusSchiller
If you want to PM me the instructions and maybe a couple screenshots, that might work for me in the interim? I'm not completely illiterate with power automate, so we can give that a shot and see if it works :)
@@jonsandler2878 basically, you'll need to use the select action on your second array and the output should be a concate() of your properties, that you want to compare. The second action will be a filter of your first array, where you use the output of the select action and use the "does not contain" option and the value to test it against needs to be the exact concate() of the same properties in the same order. So you simplify the objects to strings and compare strings against each other.
Thank you!
This is exactly what I'm looking for but still unable to achieve correct results. We have 93 vehicles in our fleet. Every week, each vehicle should be inspected. I'm trying to identify vehicles that have not been inspected based on (3) arrays. varVehListNos is an array that is a listing of all vehicles by VehNo. varVehInspNos is an array that is a listing VehNo that have an inspection from the week. varVehInspMissNos should be an array of VehNo from varVehListNos that are not present in varVehInspNos. Any suggestions would be appreciated.
Hi! Are your arrays "varVehListNos" and "varVehInspNos" simple arrays, or do they contain objects. I've made a very simple flow to demonstrate it with simple arrays:
Trigger is "Manually trigger a flow"
First Action: initialize array variable "varVehListNos" with values:
[
"Car01",
"Car02",
"Car03",
"Car04",
"Car05",
"Car06",
"Car07",
"Car08",
"Car09",
"Car10"
]
Second Actions: initialize array variable "varVehInspNos" with values:
[
"Car02",
"Car04",
"Car06",
"Car08",
"Car10"
]
Comment: So every second car has been inspected and cars 1,3,5,7 and 9 should be the ones that haven't been inspected.
Third Action: Filter array:
"From"-Value: varVehListNos
Comment: In your case that array should have all the cars available anyway
Filter - click on "Edit in advanced mode"
Paste this in: @or(
not(contains(variables('varVehListNos'), item())),
not(contains(variables('varVehInspNos'), item()))
)
Test it and the output should be:
[
"Car01",
"Car03",
"Car05",
"Car07",
"Car09"
]
Hello, thank you for sharing your technique.
I wonder if it works with 2 arrays containing Object ?
Hi! Yes and no. There is a workaround that I've come up with and I've started to make a video about it, but I haven't finished it. It's basically turning the properties of the objects of your two arrays into a single string and make an array out of that and then compare it. The tricky part is, to select the object properties that you want to compare and create the identical structure, because otherwise the comparison would not work.
Hi, could you please advise, how do I use the contains, if my arrays have more than 1 column.
Say,i just want to look at column 1 (e.g. Last name) of both arrays, even though their column 2 are totally unrelated.
Hi, the problem is that you will need to simplify one of your arrays. Let's say you want to filter array1 and compare it with the values of array2. Then use a "Select" action on array2, to get a one-dimensional array of all your LastNames of array2. In the "Filter array" action, you will filter your array1, then use the output of the "Select" action in the first field, compare it with "contains" and in the last field you will enter "item()?['LastName']". That will give you back all the items of array1, where the LastName is also in array2
when I'm importing the list from SharePoint, then these methods ain't working
You'll need to provide some more details why these are not working for you. I'm using these methods in a few of my projects that also include arrays from SharePoint lists/libraries or other sources that provide more complex arrays consisting of objects. You'll have to break them down to more simple arrays and extract the information with "Filter array" or "Select".
Thank you!