Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Table filtering options - delete rows with specific text

Hello,

I was wondering if there is some easy way how to filter table in the advanced filtering mode for text, when I don´t want to keep some rows?

I have table with different combinations of channels (direct, email, paid search etc) and campaigns (1,2,3). My problem is that I want PowerBI to not show me Paid search for campaign 3. So far I am doing it by the advanced search method, but there I would have fill the rows that I WANT TO KEEP. So is there an option on how can I easily unfilter just rows with paid search and campaign 3?

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    You can deal with it in Power Query Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjYwVNJRSsksSk0uATIMlWJ1QIJGQHZqbmJmDpKYMZBdkJiZolCcmliUnIEkY4JshBFU0BTJCJiYGYYRMBlzZCOMoYIWSEbAxCwxjADKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer = _t, #"combinations of channels" = _t, campaigns = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer", type text}, {"combinations of channels", type text}, {"campaigns", Int64.Type}}),
    #"FilteredRows" = Table.SelectRows(#"Changed Type", each not(Text.Contains([combinations of channels], "paid search") and [campaigns] = 3))
in
    #"FilteredRows"

    In addition, you can refer the solution in the following links to get it.

    "Does Not Contain" text filter filters too much

    Power Query Filter Rows by NOT Contains Criteria - Single Formula Solution

    Select Rows if text Contains (Value of a list)

    Best Regards

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Based on what I got

    If you want to completely remove those. Filter those out in the power query.

    If you want at page or all page level then use filter pane for that

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you and is there an easy way how to filter it? So far I am doing through the advanced filtering options and putting down all the rows I want to keep, which is a bit time consuming.

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Select the visual and add the fields for channel and campaign to the filters for the visual. Then select Paid for channel and 3 for campaign in the corresponding filters. 
        You can use this method for reports pages or the whole report (you will see the options in the filter pane). However, if it is for the whole report, it is best to filter the tables in Power Query instead.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    You can deal with it in Power Query Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjYwVNJRSsksSk0uATIMlWJ1QIJGQHZqbmJmDpKYMZBdkJiZolCcmliUnIEkY4JshBFU0BTJCJiYGYYRMBlzZCOMoYIWSEbAxCwxjADKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [customer = _t, #"combinations of channels" = _t, campaigns = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer", type text}, {"combinations of channels", type text}, {"campaigns", Int64.Type}}),
    #"FilteredRows" = Table.SelectRows(#"Changed Type", each not(Text.Contains([combinations of channels], "paid search") and [campaigns] = 3))
in
    #"FilteredRows"

    In addition, you can refer the solution in the following links to get it.

    "Does Not Contain" text filter filters too much

    Power Query Filter Rows by NOT Contains Criteria - Single Formula Solution

    Select Rows if text Contains (Value of a list)

    Best Regards