cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EliskaRychetska
Frequent Visitor

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.

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @EliskaRychetska ,

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"

yingyinr_0-1634632985041.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
yingyinr
Community Support
Community Support

Hi @EliskaRychetska ,

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"

yingyinr_0-1634632985041.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@EliskaRychetska , 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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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.

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!