cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olimilo Member
Member

Exclude all items found in list in the dataset (Power Query)

I'd like to filter items from my dataset based on a list (l_Exceptions).

 

I was trying the solution in this post (which filters the dataset to any item found in the list) but instead would like to exclude the ones that are found in the list. The code below doesn't work unfortunately. Is there another way to approach this without having to resort to a merge and a custom column?

 

= Table.SelectRows(#"Filter InspectionType", each List.ContainsAny(l_Exceptions, {[InspectionNo]}, false))

 

1 ACCEPTED SOLUTION

Accepted Solutions
olimilo Member
Member

Re: Exclude all items found in list in the dataset (Power Query)

Hi @Zubair_Muhammad 

 

I tried that but I'm getting an error (l_Exceptions is a list of text values, InspectionNo is a column of text values):

= Table.SelectRows(#"Expanded ExceptionList", each List.RemoveItems({[InspectionNo]}, l_Exceptions))

Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=List
Type=Type

 

Edit: So I;m getting the error from the code aboe because List.RemoveItems returns a list and I need a logical value for the Table.SelectRows. Anyway, I found my answer here: Found the solution here: https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column...

 

= Table.SelectRows(step, each List.Contains(l_Exceptions, [InspectionNo]) = false)

2 REPLIES 2
Super User
Super User

Re: Exclude all items found in list in the dataset (Power Query)

olimilo Member
Member

Re: Exclude all items found in list in the dataset (Power Query)

Hi @Zubair_Muhammad 

 

I tried that but I'm getting an error (l_Exceptions is a list of text values, InspectionNo is a column of text values):

= Table.SelectRows(#"Expanded ExceptionList", each List.RemoveItems({[InspectionNo]}, l_Exceptions))

Expression.Error: We cannot convert a value of type List to type Logical.
Details:
Value=List
Type=Type

 

Edit: So I;m getting the error from the code aboe because List.RemoveItems returns a list and I need a logical value for the Table.SelectRows. Anyway, I found my answer here: Found the solution here: https://eriksvensen.wordpress.com/2017/12/12/powerquery-filter-a-table-based-on-another-table-column...

 

= Table.SelectRows(step, each List.Contains(l_Exceptions, [InspectionNo]) = false)