Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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
@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
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.
Proud to be a Super User!
Paul on Linkedin.