Forum Discussion

freelensia's avatar
freelensia
Icon for Advocate II rankAdvocate II
7 years ago

Table.SelectRows if Text.Contains (or not contain) any from a list of words

I have this basic filtering line of code that will return only entries where the column Name does not contain any of the words: "changed", "old" or "GSTable"

   KeepOnlyDateText = Table.SelectRows(ShowFiles, each Text.Contains([Name], "changed") = false and Text.Contains([Name], "old") = false) and Text.Contains([Name], "GSTable") = false),

Is this possible to put these exclusion words into a list and refer to it? I tried:

KeepOnlyDateText = Table.SelectRows(ShowFiles, and List.Contains(CSVExcludeKeywords,[Name]) = false),

But this does not seem to filter anything out.

Need Help

4 Replies

  • Nolock's avatar
    Nolock
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi freelensia,

    instead of and you need an each.

     

    KeepOnlyDateText = Table.SelectRows(ShowFiles, each List.Contains(CSVExcludeKeywords,[Name]) = false),
    • freelensia's avatar
      freelensia
      Icon for Advocate II rankAdvocate II
      7 years ago

      Thanks! However I did this and it did not work.

      In this query I am invoking it as a function FilterCSVsByDate.

      (FolderPath as text, DateText as text) =>
      //Open the folder 
    ShowFiles = Folder.Files(FolderPath),

//Filter by DateText
   KeepOnlyDateText = Table.SelectRows(ShowFiles, each Text.EndsWith([Folder Path], FolderPath) and List.Contains(CSVExcludeKeywords,[Name]) = false),
    
//Get ContentCol
    AddContent = Table.AddColumn(KeepOnlyDateText, "ContentTbl", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])))

in
    AddContent

      (FolderPath is the path to the folder, to make sure that I dont pick up any files in sub-folders). And here is the result.

      As you can see the ExcludeKeywords are not excluded. Here they are:

      • freelensia's avatar
        freelensia
        Icon for Advocate II rankAdvocate II
        7 years ago

        Furthermore, I am calling this function from a text file. Here is the code to connect to the text file:

        (FolderPath as text, DateText as text) =>
  let
  GetFunction = Text.FromBinary(File.Contents(Excel.CurrentWorkbook(){[Name="PQMacrosFolder"]}[Content]{0}[Column1]&"FilterCSVsByDate.txt")),
  EvaluateFunction = Expression.Evaluate(GetFunction, #shared),
  EvaluateFunction2 = EvaluateFunction(FolderPath, DateText)
in
  EvaluateFunction2

        I had to Evaluate the function twice, cuz for once it will not work (only show the function screen again, does not evaluate the inputs).

        However a strange thing is that when I do this with a function written inside PQ Editor, it filters correctly. It does not work when I call the function from a text file (method above).