Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Powerquery > Advanced Filter not working with multiple clauses

I have a large list of product data from Amazon (5000 rows) and need to remove irrelevant products based on text that shows in the product title column. So I have used powerquery and the Advanced Fi...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    =Table.SelectRows(#"Trimmed Text", each not List.Contains({"Word 1","Word 2","Word 3"},[Title],(x,y)=>Text.Contains(y,x)))

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test. For you relevant step is Custom1

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKUQjPL0pRMFSK1YlWAjFNzUzA7JTUNIiUhUJGVgpYyFEhKT8/G6LSP8hFwdAMKJWTqhQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Title", Text.Trim, type text}}),
    Custom1 = Table.SelectRows(#"Trimmed Text", (x) => not List.AnyTrue(List.Transform({1..17}, each Text.Contains(x[Title], "Word " & Text.From(_), Comparer.OrdinalIgnoreCase))))
in
    Custom1