Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago
Solved

Table not contains

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

 

RefColor
AYellow of sun, car in red, Black for night
APink color, yellow is fun, not in Red, Black only
aWhite paper and Black panther
Bgreen, orange juice and grey
cgreen, orange juice and grey
CPink color, maybe yellow, Red  shoes, Black in men
DYellow or Red
dRed and Black style

 

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    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"

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Mederic So like this?

    Table = 
  VAR __Color = MAX( 'Criteria Not Contains',[Color] )
  VAR __Result = FILTER( 'Data', NOT( CONTRAINSSTRING( [Color], __Color ) ) )
RETURN
  __Result
    • Mederic's avatar
      Mederic
      Post Patron
      2 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        2 years ago

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

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago
        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's avatar
      Mederic
      Post Patron
      2 years ago

      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's avatar
        AlienSx
        Super User
        2 years ago

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

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    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's avatar
      Mederic
      Post Patron
      2 years ago

      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's avatar
        ronrsnfld
        Super User
        2 years ago

        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"