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 Name To submit as from Submission optional (Yes/No) Lizui 02 July 2022 No Laufenburg   Yes Tegalpap...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    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"

     

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"