Filtering a column based on a list

[Connor888]

Hi! I'm trying to filter some data I have in a particular way. Namely I have a long list of ID numbers, with a test result (either Y or N), and I'd like to filter out not just every row where the result is "Y", but every row for each ID with at least one "Y", since IDs often appear more than once in the list with different results. 

The code I'm using is as follows: 

#"Select Rows" =
let
    TestList =
        {Table.Column(
            Table.SelectRows(#"Table", each [Test] = "Y"),
            "ID"
        )},
Output = Table.SelectRows(#"Table", each List.Contains(TestList, [ID]) = false)

in
    Output

However, this doesn't seem to filter my list at all, let alone correctly. Does anyone have any idea why/how I can make this work?

[Anonymous · Accepted solution]

Ho @Connor888,

The simplest way, I think, to first filter your table for "Y", then Table.Distinct it by ID and then do inner join back on the source table. This should leave only ID with at least one "Y".

The way you do it, in my view a bit more compute-hungry. I think the issue in your code is that you have Table.Column in curly brackets. From what I remember it returns a list, and you put it in another list again. Then you List.Contains on this top-level list and no wander it always returns false as it only contains a list, not scalar values that you want to look up for. Try removing the curly brackets.

You can also improve performance of you version by adding List.Distinct to TestList. It will reduce the number of elements to search through.

Also in the List.Contains... Did you mean to search for [ID]? List.Contains(TestList, [Item]) = false filters IDs with no "Y" result, is this what you intend to do?

Added the code similar to yours. This returns all [ID] which at least once have "Y" in the [Test] field. Depends on the size of your data I would probably also buffered the lookup list ( TestList = List.Buffer (List.Distinct(...)) )

TestList = List.Distinct( Table.SelectRows(Table, each ([Test] = "Y"))[ID]),
Output = Table.SelectRows(Table, each List.Contains(TestList, [ID]))

Kind regards,
JB

[Connor888]

Thanks for your reply! Doing an inner join is how I eventually solved this myself, I was hoping to find a slightly... neater way to do it I suppose, though I hadn't considered that as you say this does involve a lot of computation.