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
see if any of these queries do what you are looking for.
- Anonymous4 years agoNot applicable
Anonymous, list-to-filter-column-using-Text-Contains-function.pbix is correct. Although I don't understand the variables in your functions, I'm trying to recreate your functions. There's no parameter in your file when opened with PQ. When I right-click on the list and select "create function" in the Queries pane, the list and the created function are moved to another folder. Am I recreating your function in the right way? Thanks!
- Anonymous4 years agoNot applicable
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
- Anonymous4 years agoNot applicable
Thanks Anonymous, your functions work but extremely slow. So I'm hoping for a faster solution.
- Anonymous4 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.