Forum Discussion

aoliver's avatar
aoliver
Icon for Helper III rankHelper III
6 years ago
Solved

Select Rows if text Contains (Value of a list)

Hello everyone, ill explain my problem with examples:

 

I have a table like this:

ID - Message

1 - @asdf
2 - @8rr

3 - 674

4 - 1231@3
etc
In a List, i have the differents  values which I want to filter  of the  previous table, i.e:
List: 
@8
@3

 

What I want is filter the first table, in the case  that  the message contain  ANY of the values of the list, so my expected result would be:

1 - @asdf

3 - 674

 

Check  that its CONTAINS and not EQUAL.

Ok, lets go to M code, after my test, I could solve partially the problem, I achieve to filter if the message is Exactly the string in the List, with the following code: 

 

= Table.SelectRows(#"Replaced Value1", each not (List.Contains(List, [message])))

 

After a research, Ill try this code, but it does not work: 
= Table.SelectRows(#"Replaced Value1", each not (List.MatchesAny(List, each Text.Contains([message],_))))

 

It brings me the following error:

Expression.Error: We cannot apply field access to the type Text.

 

 

So, any ideas of how to achieve this?

 

Thanks in advance

 

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    aoliver here is how you can do it

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTQVXBILE5JU4rViVYyAvEsiorAHGMgx8zcBMw2AbINjYwNHYyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => List.MatchesAny(SearchList, each Text.Contains(x[Column1], _)))
in
    #"Added Custom"

     

    here is the query for SearchList

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrBQitUBUsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Column1 = #"Changed Type"[Column1]
in
    Column1

     

    Would appreciate Kudos 🙂 if my solution helped.

     

     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    aoliver here is how you can do it

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTQVXBILE5JU4rViVYyAvEsiorAHGMgx8zcBMw2AbINjYwNHYyVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => List.MatchesAny(SearchList, each Text.Contains(x[Column1], _)))
in
    #"Added Custom"

     

    here is the query for SearchList

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcrBQitUBUsZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Column1 = #"Changed Type"[Column1]
in
    Column1

     

    Would appreciate Kudos 🙂 if my solution helped.

     

     

    • aoliver's avatar
      aoliver
      Icon for Helper III rankHelper III
      6 years ago

      Amazing, this is exactly what I looking for, THANKS!

    • aoliver's avatar
      aoliver
      Icon for Helper III rankHelper III
      6 years ago

      parry2k 

      Sorry for mention you again, my  problem has changed a little:

      It is possible to filter 2 times in 2 different columns? Let me explain with an example:
      My table
      ID - STATUS 1 - STATUS 2
      1 - TD - #8

      2 - TD - #5

      3 - TM - #5

      I want to filter with AND not with Any, for example (in my  list) 

      List 1      List2

      TD          #8

      TM          #5

      So, List1 filters status1 and List 2 filters status2,

       

      so the result would be
      2 - TD - #5

       

      There is a problem: the value of the list is not the whole value of status columns, its just a part of it.

       

      Have the example sense? Any ideas? 
      Ive tried to create 2 conditional columns but that is a OR not an AND, in logical forms.

      Thanks again