Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter table based on another table value

Hello,

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

 

Student NameTo submit as fromSubmission optional (Yes/No)
Lizui02 July 2022No
Laufenburg Yes
Tegalpapak29 August 2022No
Ar Rabiyah  
Bellegarde02 July 2022 
Gangarampur No
Luntas05 May 2022Yes
Frei Paulo10 August 2022Yes
Seedorf03 July 2022No
Cosamaloapan de Carpio10 September 2022No

 

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!

Need Help

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"