cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jakenl79
Helper I
Helper I

Filter array with another array with 2 matching fields

I have 2 arrays:

1, with columns:

Project

Article

Description

Code

 

2, with columns:

Project

Article

 

I would like to filter Array 1 with array 2 where both project and article match (the same article can be used in multiple projects, so only a matching project-article should be used as filter

 

When searching for 'filter array with another array', the solution is always to make from array 2 a single column array, but I need 2 columns instead.

 

I spent all afternoon to make it work and it sounds so simple, but unfortunately no luck so far

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks for your extensive answer! My project/article array was the result of a similar process and my goal was just to get the full array1 back for adding all fields to a SP list.

 

After I started this thread, I finally put the laptop aside and then thought, how would I solve this in Excel? There I would concat both columns in both tables and compare it like that.

 

So, one more try and... that worked:

 

I made a select on the project/article array by only using a concat function: item()?['Project'],item()?['Article'])

Secondly, a filter on the first array where filter input was the output of the select and this was checked against 'contains'  item()?['Project'],item()?['Article'])  (this is of course from array1)

Screenshot 2022-05-28 22.42.18.png

View solution in original post

2 REPLIES 2
eliotcole
Super User
Super User

You just need to union both arrays in the From field of a Select action, and pick the Project and Article columns in the select.

 

You need to union both arrays, but you need to select the first array into two columns first.

arraymashupinnitbruv.jpg

I've just shown the two ways you can make that Project/Article map there. 

arrayOneCNST example data:

Spoiler
[
  {
    "Project": "Project 1",
    "Article": "Article 1",
    "Description": "Some words.",
    "Code": "1234567"
  },
  {
    "Project": "Project 2",
    "Article": "Article 1",
    "Description": "Some words.",
    "Code": "1234567"
  },
  {
    "Project": "Project 3",
    "Article": "Article 2",
    "Description": "Some words.",
    "Code": "1234567"
  },
  {
    "Project": "Project 4",
    "Article": "Article 3",
    "Description": "Some words.",
    "Code": "1234567"
  },
  {
    "Project": "Project 7",
    "Article": "Article 2",
    "Description": "Some words.",
    "Code": "1234567"
  },
  {
    "Project": "Project 8",
    "Article": "Article 3",
    "Description": "Some words.",
    "Code": "1234567"
  }
]

arrayTwoCNST example data:

Spoiler
[
  {
    "Project": "Project 1",
    "Article": "Article 1"
  },
  {
    "Project": "Project 2",
    "Article": "Article 1"
  },
  {
    "Project": "Project 3",
    "Article": "Article 2"
  },
  {
    "Project": "Project 4",
    "Article": "Article 3"
  },
  {
    "Project": "Project 5",
    "Article": "Article 1"
  },
  {
    "Project": "Project 6",
    "Article": "Article 1"
  }
]

OK, so for some odd reason the union() function wasn't working normally, so I added an extra Select action to bring array 1 in line with array 2 in terms of how it looks.

 

In Select arrayOneCols it uses arrayOneCNST in the From field, and each key is literally just item()?['Product'] and item()?['Product']. If you paste the below into the text view (that little 'T' icon on the side) it should just build it just like the bottom one:

{
  "Project": @{item()?['Project']},
  "Article": @{item()?['Article']}
}

Then, finally, in the asda action, this is how you make the union() that's in the From field, with the Map being identical to the other one:

union(body('Select_arrayOneCols'), outputs('arrayTwoCNST'))

 

If you want all the columns back from the first array, though, it'll get a little more complicated, but still doable.

Thanks for your extensive answer! My project/article array was the result of a similar process and my goal was just to get the full array1 back for adding all fields to a SP list.

 

After I started this thread, I finally put the laptop aside and then thought, how would I solve this in Excel? There I would concat both columns in both tables and compare it like that.

 

So, one more try and... that worked:

 

I made a select on the project/article array by only using a concat function: item()?['Project'],item()?['Article'])

Secondly, a filter on the first array where filter input was the output of the select and this was checked against 'contains'  item()?['Project'],item()?['Article'])  (this is of course from array1)

Screenshot 2022-05-28 22.42.18.png

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (4,545)