Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pkoel-asb
Helper V
Helper V

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

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

2 ACCEPTED SOLUTIONS
watkinnc
Super User
Super User

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!


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

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.

 

View solution in original post

7 REPLIES 7
edhans
Super User
Super User

Note that @watkinnc 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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!


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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.

 

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

This works:

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors