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
olimilo
Responsive Resident
Responsive Resident

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

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)

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

Hi @olimilo 

 

You can use List.RemoveItems

 

https://docs.microsoft.com/en-us/powerquery-m/list-removeitems

 


Regards
Zubair

Please try my custom visuals

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)

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.