Table not contains

[Mederic]

Hello everyone,
How can I get the result in the image below?
I actually have a table with several columns and around 2000 rows.
The problem here is that I'm looking for a word in a group of words or sentences, regardless of the case.

Thanks in advance

Best regards

Ref	Color
A	Yellow of sun, car in red, Black for night
A	Pink color, yellow is fun, not in Red, Black only
a	White paper and Black panther
B	green, orange juice and grey
c	green, orange juice and grey
C	Pink color, maybe yellow, Red  shoes, Black in men
D	Yellow or Red
d	Red and Black style

[ronrsnfld · Accepted solution]

You implied you wanted to hard-code the excludes within the M-Code itself. So perhaps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDoIwEEWvMum6lxA9gHFjDGFRywCVOkOmJYbb2xYMxpXbn/cmL1PX6qC0uqH3/ALuIMykwRoBRyDYaqi8sSN0LECuH6Jq9KqcHY1g2bNoWFbdBeiyThyzftl1Jr8U0yTzOriIMJkJBQy1GzIZigNKoapE9YKYbrEY6hEes7NY6LSvp+w/0PGn9GmWO269OhcChIExfEJT9hOpqKevv0hGy9qmNWt7eIiLR9U0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Color = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", type text}, {"Color", type text}}),
    
    #"Select by Criteria" = Table.SelectRows(
        #"Changed Type",
        each not List.Contains({"A","D"},[Ref],Comparer.OrdinalIgnoreCase)
             and not List.ContainsAny(Splitter.SplitTextByAnyDelimiter({" ",","})
                    ([Color]),{"pink","yellow"},Comparer.OrdinalIgnoreCase)
                
    )
in
    #"Select by Criteria"

[Mederic]

Hello @ronrsnfld ,

Thank you for your time,
It's perfect, it works very well 😊,

I'd be interested to see @AlienSx's proposal based on a table of criteria.

I'm going to work on it this evening at home to see if I can adapt his code 😊

Best regards

[ronrsnfld]

It is trivial to modify my routine if you want to use a Table as a data source for the filter.

Assume we have a table named "Filter" that looks like:

Two small changes in the M-Code to reference the Filter table and relevant column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDoIwEEWvMum6lxA9gHFjDGFRywCVOkOmJYbb2xYMxpXbn/cmL1PX6qC0uqH3/ALuIMykwRoBRyDYaqi8sSN0LECuH6Jq9KqcHY1g2bNoWFbdBeiyThyzftl1Jr8U0yTzOriIMJkJBQy1GzIZigNKoapE9YKYbrEY6hEes7NY6LSvp+w/0PGn9GmWO269OhcChIExfEJT9hOpqKevv0hGy9qmNWt7eIiLR9U0bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Color = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", type text}, {"Color", type text}}),
    
    #"Select by Criteria" = Table.SelectRows(
        #"Changed Type",
        each not List.Contains(Filter[Ref],[Ref],Comparer.OrdinalIgnoreCase)
             and not List.ContainsAny(Splitter.SplitTextByAnyDelimiter({" ",","})
                    ([Color]),Filter[Color],Comparer.OrdinalIgnoreCase)
                
    )
in
    #"Select by Criteria"

[Mederic]

Hello @ronrsnfld ,

Thank you for this proposal based on a table of criteria, it's perfect.
In my real file I have a few cells with the slash symbol / (see screenshot)
It's very easy to add it to your code and it works very well

Thanks again

Best regards

[AlienSx]

    criteria = List.Buffer(criteria_table[Color]),
    result = Table.SelectRows(
        data_table, 
        (w) => List.PositionOf(
            criteria,
            w[Color],
            Occurrence.First,
            (x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
        ) = -1
    )

[Mederic]

Hello @AlienSx ,

Thank you very much for your solution,
How can we add a 2nd criteria for the [Ref] AND [Color] columns?

Unless I'm mistaken, there is only one criteria [Color] in your proposal

Thank you in advance

Best regard

[AlienSx]

yes, my bad. But why AND? You eliminated [Ref = "a", Color = "white paper and black panther"]. Condition must be OR, isn't it? 

[Mederic]

@AlienSx 

With the advanced filters, I use the display below, which also works for me
I did a test with this example (see screenshot)
The answer to your question is that the criteria are [Ref] AND [Color].

Best regards

[AlienSx]

okay

    refs_list = List.Buffer(criteria_table[Ref]),
    colors_list = List.Buffer(criteria_table[Color]),
    not_contains = (lst, txt) => not List.Contains(lst, txt, (x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)),
    select = Table.SelectRows(
        data_table, 
        (x) => not_contains(refs_list, x[Ref]) and not_contains(colors_list, x[Color])
    )

[Mederic]

@AlienSx ,
Great solution too,
If my criteria change over time, I'll have 2 ways of doing that
You all amaze me with your solutions.
Thanks to all

Best regards

[Greg_Deckler]

@Mederic So like this?

Table = 
  VAR __Color = MAX( 'Criteria Not Contains',[Color] )
  VAR __Result = FILTER( 'Data', NOT( CONTRAINSSTRING( [Color], __Color ) ) )
RETURN
  __Result

[Mederic]

Hello @Greg_Deckler ,
Thank you for your DAX proposal,
I would like a solution with Power Query even though I also like DAX 😊

Best regards

[Greg_Deckler]

@Mederic Missed what forum it was in. So are those two tables and you are going to do a join on them or ?

[Mederic]

Hello @Greg_Deckler ,
Sorry, in the example I put a table of criteria but in reality I'd like to write criteria it in the M code without using a join

Best regards