Forum Discussion
Filter: Table does not contain any value from named range
Hello,
i am trying to filter a table, so that I only get the rows which do not contain any word from a named range.
= Table.SelectRows(#"Sorted Rows", each not List.Contains(Table.ToRows(Excel.CurrentWorkbook(){[Name="FilterWords"]}[Content]), [Signal_Name]))
I believe that the List.Contains only filters for a direct match and would not wor with the case that the filter word is contained in a cell value.
For Example my FilterWords are: VDD and GND
I want to eliminate all Signal_Names that are like 3V3_VDD or VDDQ or ISO_GND and so on.
Thank you for your Support.
Yes, you are correct that List.Contains will look for complete match. For actual contain, you will need to use List.FindText.
Hence, if a list is {johndoe, don, doejohn}, then looking for doe throgh List.FindText will return johndoe and doejohn. For for List.Contain, this won't return anything for doe.
2 Replies
Yes, you are correct that List.Contains will look for complete match. For actual contain, you will need to use List.FindText.
Hence, if a list is {johndoe, don, doejohn}, then looking for doe throgh List.FindText will return johndoe and doejohn. For for List.Contain, this won't return anything for doe.
rkords List.FindText is nice function for this particular case.
However, if the filtering criteria are a bit more complex, then you can use the more general function Text.Select which allows you to define whatever selection function you like. For example, instead of
List.RemoveItems(list, List.FindText(list, "VDD") & List.FindText(list, "GND"))you could write
List.Select(list, each not (Text.Contains(_, "VDD") or Text.Contains(_, "GND")))
