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
Delete the last step (the table select rows)
Add a column with the search i.e.
(x) => List.AnyTrue(List.Transform(searchfor, each Text.Contains(x[precinct_code], _)))
that should show true/false depending on if a list item is found in the text.
Retain the false records by filtering using the dropdown from the header of the column.
- Anonymous4 years agoNot applicable
Ok @HotChili, I'm deleting the "Custom1" step. Then I'm adding a custom column with the step:
= Table.AddColumn(#"Removed Duplicates", "Custom", each (x) => List.AnyTrue(List.Transform(searchfor, each Text.Contains(x[precinct_code], _))))This step creates a column with "function" as values. However, the custom column's format type won't let me change to "True/False". So I can't filter the column at all.
- HotChilli4 years ago
Community Champion
That's not the same as the code I provided. You've added something. Remove it and it'll work.
- Anonymous4 years agoNot applicable
No @HotChilli, the rest was automatically added by PQ. My changes are:
- Delete "Custom1" step.
- Click on button for "custom column" in "add column" menu.
- Copy-and-paste your formula into the custom column's dialog box.
- Click on the format icon for the "custom" column.