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 ...
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"
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 ?

      • Mederic's avatar
        Mederic
        Post Patron
        2 years ago

        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