Forum Discussion

mwaltercpa's avatar
mwaltercpa
Icon for Advocate III rankAdvocate III
4 years ago
Solved

Remove rows based on column values that contain keywords from a list/table.

I would like to feed my query a list of key words i.e. "Store Acct" and "Do Not Use", and eliminate the rows in my table where those key words occur within the column [Rep Name].

Rather than go through the filter column advanced editor and key in every 'and' condition, I'd like to just maintain a list of keywords (see query below), then use that list to filter through my [Rep Name] column. Is this possible? The below query works only when I include a listing of the entire contents of the field to remove. - Many thanks!

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    Here's one way to do it in the query editor with a custom filter expression.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVcExOLlHwz0tVitVBEQopzwcLueQr+OWXKIQWp8JVIQnBVHnlQ6R8E4sqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rep Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each let thisrep = [Rep Name] in List.Count(List.Select({"Do Not Use", "Store Acct"}, each Text.Contains(thisrep, _))) = 0)
in
    #"Filtered Rows"

     

    Pat

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Here's one way to do it in the query editor with a custom filter expression.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVcExOLlHwz0tVitVBEQopzwcLueQr+OWXKIQWp8JVIQnBVHnlQ6R8E4sqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rep Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each let thisrep = [Rep Name] in List.Count(List.Select({"Do Not Use", "Store Acct"}, each Text.Contains(thisrep, _))) = 0)
in
    #"Filtered Rows"

     

    Pat

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pVcExOLlHwz0tVitVBEQopzwcLueQr+OWXKIQWp8JVIQnBVHnlQ6R8E4sqlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Rep Name" = _t]),
    #"List of Keywords" = {"Do Not Use", "Store Acct"},
    #"Selected Rows" = Table.SelectRows(Source, each not List.Accumulate(#"List of Keywords", false, (s,c) => s or Text.Contains([Rep Name], c, Comparer.OrdinalIgnoreCase)))
in
    #"Selected Rows"