Forum Discussion
list to filter column using Text.Contains function
- 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
Is the requirement to look in the text of the column precinct_code for any of the items in the searchfor list? So using Text.Contains with each item in the list?
(I didn't download the file - too big)
HotChilli, the data source is on the web. I've inserted the "not" operator into your solution. By doing so, I've inverted the filter logic to be: "not" and "contains any" (including partial matches of the items in the 'searchfor' list. The filter logic is the last transformation step ("Custom1") in the following M code:
let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://s3.amazonaws.com/dl.ncsbe.gov/ENRS/2020_11_03/results_precinct_sort/STATEWIDE_PRECINCT_SORT.txt"), null, null, 1252)}),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "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"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", 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"),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"precinct_code"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
Custom1 = Table.SelectRows(#"Removed Duplicates", (x) => List.AnyTrue(List.Transform(searchfor, each not Text.Contains(x[precinct_code], _))))
in
Custom1
The 'searchfor' list (item 'PROVI ' has a trailing space character) is:
| OS |
| ABS |
| CURBSIDE |
| ONE STOP |
| OS |
| OS- |
| OSAP |
| OSCA |
| OSCH |
| OSKD |
| OSLL |
| OSLOB |
| OSNR |
| OSOP |
| OSTA |
| OSWA |
| PROVI |
| PROVISIOINAL |
| PROVISIONAL |
| TRANS |
However, it's not correctly filtering out the partial matches below:
precinct_code
| ABSENTEE |
| ABSENTEE BY MAIL |
| DOB OS |
| ELK OS |
| MA OS |
| MCGEE OS |
| OFFICE OS |
| ONE STOP |
| ONE STOP ARB |
| ONE STOP CBC |
| ONE STOP CBR |
| ONE STOP CFC |
| ONE STOP CFN |
| ONE STOP DANBURY |
| ONE STOP EES |
| ONE STOP GVT |
| ONE STOP KING |
| ONE STOP MSL |
| ONE STOP NLB |
| ONE STOP PVL |
| ONE STOP SRC |
| ONE STOP WALNUT COVE |
| OS AG CENTER |
| OS CJRC |
| OS COMM SCHOOLS |
| OS DTM |
| OS DUKE |
| OS ENO |
| OS EPPES |
| OS ERL |
| OS FARMVILLE |
| OS GASTON |
| OS GETG |
| OS HVBC |
| OS JACKSON |
| OS LOB |
| OS LW |
| OS ML |
| OS MSUMC |
| OS NCCU |
| OS NRL |
| OS POW |
| OS RICHSQUARE |
| OS SEVERN |
| OS SHS |
| OS SRL |
| OS STUDENT CENTER |
| OS TRC |
| OS WILLIS BUILDING |
| OS WINTERVILLE |
| PM OS |
| PROVISIONAL |
| TRANSFER |
These partial matches would also result if I were to delete the "not" operator from the last transformation step. So why isn't the "not" operator working in the last transformation step? Thanks (and same thanks if AlexisOlson can troubleshoot)!