Forum Discussion
Anonymous
4 years agoNot applicable
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)....
- Anonymous4 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 tcaread 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
Anonymous
4 years agoNot applicable
Thanks Anonymous, your functions work but extremely slow. So I'm hoping for a faster solution.
Anonymous
4 years agoNot applicable
I don't think most of the time is spent running the lines I added.
This part of the code intervenes to search a table of 264 rows (after having filtered the initial table and removed the duplicates) using a list of 20 elements. This is done in no time.
Most of the time it is in the downloading of the huge table from the site.
Try to download the table locally and have the code run locally.