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)....
  • 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