How can I do a ' not in' in Power Query - New User

[Anonymous]

Hello,

   I am a new Power Query user. I a list of users in a list titled HomeOffice. I have a table of information for my SalesTeam. Some of our SalesTeam members are also in the HomeOffice list. I want to filter the SalesTeam table and remove the users in my HomeOffice list. Here is what I have so far:

let
Source = #"Merge of AD Users & Groups",
#"SA tbl" = Table.SelectRows(Source, each [Group Name] = "Sales Associates"),
#"HomeOffice" = #"Test Home Office",

in
#"SA tbl"

I am having trouble trying to filter the SA list[member name] with the values in the HomeOffice list. Basically I want all the data in the SA tbl except for SA tbl[member names] in the HomeOffice List. How do I do this?

Cheers,

Peter

[Anonymous · Accepted solution]

Indeed, it's the same concept.  If you have TableA, with columns Name, Column2, and Column3, and another table, TableB, with columns Names and Departments.  Any column in any table can be referred to as a list using the Table[ColumnName] syntax.  So:

Table.SelectRows(TableA, each not List.Contains(TableB[Names], [Name]))

TableB[Names] is the column from the other table that you are referring to as a list, and [Name] is the column in your current table that you are turning into a list to compare to the first list.

Very useful!--Nate  P.S. Please mark these as the answer.  Thanks!

[edhans]

Note that @Anonymous has the right of it, but I have one suggestion to make. Change your code to this:

#"Filter2" = 
    Table.SelectRows(
        #"Changed Type1", 
        each not List.Contains(
            List.Buffer(#"ASB Home Office"[group.member.displayName]),
            [group.member.displayName]
        )
    )

I added returns and spaces for readability, but the difference is the List.Buffer. I have seen massive performance improvements in List.Contains when used with a buffered vs raw list, especially if the list is from a relational database where query folding is still happening.

[Anonymous]

True, @edhans, I do always buffer my lists!

[Anonymous · Accepted solution]

Indeed, it's the same concept.  If you have TableA, with columns Name, Column2, and Column3, and another table, TableB, with columns Names and Departments.  Any column in any table can be referred to as a list using the Table[ColumnName] syntax.  So:

Table.SelectRows(TableA, each not List.Contains(TableB[Names], [Name]))

TableB[Names] is the column from the other table that you are referring to as a list, and [Name] is the column in your current table that you are turning into a list to compare to the first list.

Very useful!--Nate  P.S. Please mark these as the answer.  Thanks!

[Anonymous · Accepted solution]

Hello,

   I change the #ASB Home Office" source back to a table. I went into the advanced editor and tried the following:

#"Filter2" = Table.SelectRows(#"Changed Type1", each not Table.Contains(#"ASB Home Office"[group.member.displayName],[group.member.displayName]))

and I get the error:

Expression.Error: We cannot convert a value of type List to type Table.

I seem to have a data-type mismatch in that line of code.

[Anonymous]

Change Table.Contains to List.Contains. 
--Nate

[Anonymous]

This works:

Table.SelectRows(#"SA tbl", each not List.Contains(NameOfYourHomeOfficeList, #"SA tbl"[member names]))

[Anonymous]

Hello,

   Thank you. that did work for me. Can you now show me how to do the same task, but with two tables instead of a list and a table?

Cheers,

Peter