Forum Discussion

Rich_Wyeth's avatar
Rich_Wyeth
Icon for Helper I rankHelper I
1 year ago
Solved

Removing Rows with Certain Values

Hi all,   I have set up some filters on my Data which don't appear to work. Below is what I have done, one by one removing any data with the Month named in the column. I have followed on the sequ...
  • wardy912's avatar
    1 year ago

    The issue in your code is the use of the or operator in your Text.Contains conditions.

     

    each not Text.Contains([Cust PO Number], "JAN") or not Text.Contains([Cust PO Number], "JANUARY")

     

    This will always return true unless the value contains both "JAN" and "JANUARY" at the same time:

     

    If [Cust PO Number] contains "JAN" but not "JANUARY", the second condition is true, so the whole expression is true.
    If it contains "JANUARY" but not "JAN", the first condition is true, so again the whole expression is true.
    So the row is not removed.


    You should use and instead of or to ensure the row is removed if it contains either "JAN" or "JANUARY".

     

    #"Remove CONS - JANUARY" = Table.SelectRows(#"Remove 0 Price", each not Text.Contains([Cust PO Number], "JAN") and not Text.Contains([Cust PO Number], "JANUARY")),
    #"Remove CONS - FEBRUARY" = Table.SelectRows(#"Remove CONS - JANUARY", each not Text.Contains([Cust PO Number], "FEB") and not Text.Contains([Cust PO Number], "FEBRUARY")),


    Please give a thumbs up if this fixes your issue.