Filter array with another array with 2 matching fields

[jakenl79]

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

[eliotcole]

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.

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.

[jakenl79 · Accepted solution]

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)