How do I filter multiple rows ?

[EaglesTony]

Hi,

  I have the following columns in my data:

ID              Label1                           Label2

123           GREEN, YELLOW            BLUE,RED

456           ORANGE                        GREEN

456           GREEN                           PURPLE

456           RED                                BLUE

456          RED                                 YELLOW

What I am trying to do is combine filters so to speak, for example

Filter #1: Label1 contains GREEN or YELLOW

Filter #2: Label2 contains GREEN or YELLOW

This would results would be:

123

456 (Where Green is in Label2)

456 (Where Green is in Label1)

456 (Where Yellow is in Label2)

[dufoq3]

Hi @EaglesTony, try this.

Now you can just filter value 1 in [Keep Row] column.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcg9ydfXTUYh09fHxDwfynXxCXXWCXF2UYnWilUxMzYBC/kGOfu6uILVFqal5SBJgvUA6IDQowMcVSQKkH2IUhiDUothYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Label1 = _t, Label2 = _t]),
    Ad_KeepRow = Table.AddColumn(Source, "Keep Row", each 
        [ a = Text.SplitAny([Label1], " ,") & Text.SplitAny([Label2], " ,"),
          b = if List.ContainsAny({"GREEN", "YELLOW"}, a, Comparer.OrdinalIgnoreCase) then 1 else 0
        ][b], Int64.Type)
in
    Ad_KeepRow

[EaglesTony · Accepted solution]

I was able to solve it doing the following:

1) Select both columns

2) Drop down one of the columns and do "Text Filters" and click on "Advanced"

Now it allows to select multiple columns.