See who is not in a list

[Matthias93]

Hi,

I have a query containing 2 SharePoint lists. One lists contains all members of a group. The second list contains information on their performance, this list only shows a line of information for each member that has had an evaluation. I would however like to be able to see who has not had an evaluation. So basically I need a visual (table), to show me all people who are in the members list, but not in the evaluation list. The Member Name is the common column that can be found in both lists.

Can somebody help me with this?

Kind regards,

Matt

[apo1979prio]

Table.AddColumn(Step20, "VP", each if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 

    List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3)))  then "E"

else if (List.Contains({"ZNLD","ZNLM"},[DOC_TYPE]) and 

    not(List.Contains({"500","699","800","999","PT5","PT6","PT8","PT9"},Text.Middle([#".NIF_Venda"], 3, 3))))  then "P"

else if List.Contains({"ZTOP","ZREP","ZG2P","ZGCP"},[DOC_TYPE]) then "F"  //Frota

else null)

[BhaveshPatel]

Hi Matt

It would be more appreciated if ypu can post the sample data.

[Matthias93]

Hi,

This is a simplified version of my data-model, but I think it shows the essentials. I would like to be able to see all members that do not have a line in the scoring list.

Kr,

Matt

[BhaveshPatel · Accepted solution]

Hi Matt

You can use Query Editor for this.

Go to the Query Editor and Use Merge Queries as New option.

Merge Table 1 with Table 2( Include Scores), Select Team Member column in Both the Queries, Use Left Outer Joiin.

In the Resulting Query, Filter out all the null score values and delete the score column. This will give you the Team Members not present in the Second Table from the first table.

Left Outer JoinFiltering null values after expanding the merged column