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
AlexisOlson
Super User
4 years agoBear in mind that M is case-sensitive. Without seeing an example, I can't say that this is the problem, but it does come up frequently with this sort of thing.