Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

list to filter column using Text.Contains function

I have a query with a column that I want to filter based on a list that has multiple items.  I want to use the filter logic "contains any" (including partial matches of the 'searchfor' list's items)....
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    this is the code of the funcion not_cnt(...).

    open an empty query and copy and paste inside this code.

    The same if you want the cnt(...) function.

     

    let
    tca = (txt, lst)=>List.Accumulate(lst,true, (s,c)=>not Text.Contains(txt,c) and s )
in
    tca

     

    read the comment inside (text after "//")

     

     

    "sf" is the name I used for your list "searchfor". Change it as need

     

        Origine = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\sprmn\Downloads\STATEWIDE_PRECINCT_SORT.txt"), null, null, 1252)}),
    #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(Origine, "Column1", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19"}),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}, {"Column1.16", type text}, {"Column1.17", type text}, {"Column1.18", type text}, {"Column1.19", type text}}),
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(#"Modificato tipo", [PromoteAllScalars=true]),
    #"Modificato tipo1" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"county_id", Int64.Type}, {"county", type text}, {"election_dt", type date}, {"result_type_lbl", type text}, {"result_type_desc", type text}, {"contest_id", Int64.Type}, {"contest_title", type text}, {"contest_party_lbl", type text}, {"contest_vote_for", Int64.Type}, {"precinct_code", type text}, {"precinct_name", type text}, {"candidate_id", Int64.Type}, {"candidate_name", type text}, {"candidate_party_lbl", type text}, {"group_num", Int64.Type}, {"group_name", type text}, {"voting_method_lbl", type text}, {"voting_method_rslt_desc", type text}, {"vote_ct", Int64.Type}}),
    #"Filtrate righe" = Table.SelectRows(#"Modificato tipo1", each ([county] = "BERTIE" or [county] = "DURHAM" or [county] = "MARTIN" or [county] = "NEW HANOVER" or [county] = "NORTHAMPTON" or [county] = "PITT" or [county] = "STOKES" or [county] = "SURRY" or [county] = "TYRRELL" or [county] = "WILKES")),
    #"Rimosse altre colonne" = Table.SelectColumns(#"Filtrate righe",{"precinct_code"}),
    #"Rimossi duplicati" = Table.Distinct(#"Rimosse altre colonne"),

// I changed only the followings lines 
// here not_cnt is called. If you want cnt(...), juats change the name of teh function called

    tsr=Table.SelectRows(#"Rimossi duplicati", each not_cnt(_[precinct_code],sf))
in
    tsr

     

     

HotChilli's avatar
HotChilli
Community Champion
4 years ago

The function from the initial post:

List.Contains(searchfor, [precinct_code]

is going to look in the list searchfor for exact matches of the precinct_code field but I think you want to look in the precinct_code field (text) in each row for any of the items in the list searchfor (I could be getting us both confused at this stage!)

Let me give you this M and see if it's what you need:

Table.SelectRows(#"Changed Type1", (x) => List.AnyTrue(List.Transform(searchFor, each Text.Contains(x[group_name], _))))

you will edit the Changed Type1 to be the previous step at your side.  group_name was the field from the 2nd file you provided (I think you edited your post to remove it but I was fast) so you might need to change that.

I'm logging off now but if you need more help just post back and I'll look over the weekend.

All the best.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks HotChilli, my M knowledge is too simple so I didn't realize that I didn't even pick the correct function.  I'm trying to understand just one of the functions in your solution.  What are the arguments in Text.Contains?

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      Since there are two contexts/environments here (the row context and the list context), you have to distinguish them. The x variable is representing the row context and the _ is the default variable for the "each" shortcut expression.

       

      You could rewrite it as:

      (row) => List.AnyTrue(List.Transform(searchFor, (item) => Text.Contains(row[group_name], item)))

       Here, row[group_name] is the value for the [group_name] column within that row and item represents each item in the list searchFor and the transformation says that for each item in this list, check if row[group_name] contains that item.

       

      Recommended  reading (part 3 in particular):
      https://bengribaudo.com/blog/2017/11/17/4107