Filter table based on another table value

[Anonymous]

Hello,

I have the following "Reference" table loaded in Power Query.

Student Name	To submit as from	Submission optional (Yes/No)
Lizui	02 July 2022	No
Laufenburg		Yes
Tegalpapak	29 August 2022	No
Ar Rabiyah
Bellegarde	02 July 2022
Gangarampur		No
Luntas	05 May 2022	Yes
Frei Paulo	10 August 2022	Yes
Seedorf	03 July 2022	No
Cosamaloapan de Carpio	10 September 2022	No

Below is the "Submissions" table.

Student Name

Lizui

Laufenburg

Ar Rabiyah

Bellegarde

Gangarampur

Luntas

Seedorf

I am trying to create a new table that would filter out names in the "Submissions" table if the corresponding name in the "Reference" table in column "Submission optional (Yes/No)" is "Yes". 

The "Results" table should be as below.

Student Name

Lizui

Ar Rabiyah

Bellegarde

Gangarampur

Seedorf

Any help is much appreciated!

[Vijay_A_Verma · Accepted solution]

Here is the code for 3rd table named Results. See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

let
    Source = Table.NestedJoin(Submissions, {"Student Name"}, Reference, {"Student Name"}, "Reference", JoinKind.LeftOuter),
    #"Expanded Reference" = Table.ExpandTableColumn(Source, "Reference", {"Submission optional (Yes/No)"}, {"Submission optional (Yes/No)"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Reference", each ([#"Submission optional (Yes/No)"] <> "Yes")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Submission optional (Yes/No)"})
in
    #"Removed Columns"