Forum Discussion

mrgou's avatar
mrgou
Frequent Visitor
3 years ago
Solved

Filter text field if contains words from list

I have a table with two text columns. I want to filter for rows where either of these columns contain one or more words. So you can do it like this:

= Table.SelectRows(#"Merge", each Text.Contains(Text.Lower([Short Text]), "text1") or Text.Contains(Text.Lower([Long Text]), "text1") or Text.Contains(Text.Lower([Short Text]), "text2") or Text.Contains(Text.Lower([Long Text]), "text2"))

Now, is there a way to make that filtering based on a list or table of words? Since I have a list of about 10 words, it would make the code clearer and, most importantly, adding other words would be much easier.

  • mrgou try this

    let
        Source = your_table,
        words = {"text1", "text2"},
        selection = 
            Table.SelectRows(
                Source, 
                (x) => 
                    List.PositionOf(
                        words, 
                        x[Short Text] & x[Long Text], Occurrence.First, 
                        (w, z) => Text.Contains(z, w, Comparer.OrdinalIgnoreCase)
                    ) <> -1
            )
    in
        selection

2 Replies

  • mrgou's avatar
    mrgou
    Frequent Visitor

    This is million light years ahead of my skills, but it does appear to work. Thanks!

  • mrgou try this

    let
        Source = your_table,
        words = {"text1", "text2"},
        selection = 
            Table.SelectRows(
                Source, 
                (x) => 
                    List.PositionOf(
                        words, 
                        x[Short Text] & x[Long Text], Occurrence.First, 
                        (w, z) => Text.Contains(z, w, Comparer.OrdinalIgnoreCase)
                    ) <> -1
            )
    in
        selection