Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
7 years ago
Solved

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))

 

Need Help
  • olimilo's avatar
    olimilo
    7 years ago

    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-or-list-and-some-filter-ahas/

     

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

2 Replies