Filtering off two galleries

[MWong1]

Hello all,

I am currently trying to filter off of two galleries.

The first gallery has a flat list of names, and the code that it used was the result of a distinct function, so this data is being returned as a table 

>SortByColumns(Distinct(Filter('Test Data', Status="Yes"), 'System Manager '), "Result")

Bob

John

Mike

The second gallery also has a list of names that was the result of a simple filter and is returned as a table as well. 

>Filter('Test List 2', 'Role' = "System Manager")

Rachel

Bob

John

From the examples, both tables have names that overlap. I want to take just the names that are in common from the two galleries, and put them in another gallery(or overwrite one of the existing ones with the new data). 

Bob

John

How would I accomplish this using a filter(or related function)? Since they are both table data types, this complicates it for me at least

Thank You in advance 

[BCBuizer]

Hi @MWong1 ,

What you're looking for is an inner join. An example of this can be found here: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-can-I-make-an-inner-Join-os-PowerApps/td...

[MWong1]

I tried following the instructions in the linked post, but I am still having some issues. Right now, the code I have written in the test table 2 item has.

>AddColumns('Test Table 2',"NewNames",LookUp('Test Table 1 '='Test Table 2'[@Name]))

However, I don't quite understand the logic in the example and therefore have trouble implementing it. Why does the lookup function have an @[Name] in it?

Right now, the code is just returning a list of the normal names, and the new column just says [Record] all the way down 

[WarrenBelz · Accepted solution]

Hi @MWong1 ,

A bit more complex if you want to use those filters and then get the items from the second list that are in the first. You have not posted the name of the field in the second list that contains the person's name - just substitute it below

With(
    {
      w1: 
      SortByColumns(
         Distinct(
            Filter(
               'Test Data', 
               Status = "Yes"
            ), 
            'System Manager '
         ), 
         "Result"
      ),
      w2:
      Filter(
         'Test List 2', 
         'Role' = "System Manager"
      )
   },
   Filter(
      w2,
      YourNameField in w1.Result
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[MWong1]

Thank You so Much! Your help and guidance is greatly appreciated!