Forum Discussion

aoliver's avatar
aoliver
Helper 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 wa...
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.

     

     

parry2k's avatar
parry2k
Super 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
    Helper III
    6 years ago

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

  • aoliver's avatar
    aoliver
    Helper 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