Removing Rows with Certain Values

[Rich_Wyeth]

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 sequence from each month, so would expect this to have worked at the end. However, when I close and apply and search fror "JAN", my data is still returning rows. seems to be doing it for each month, I thought I had removed.

Should I be doing something different?

The remove 0 part at the top of the sequence, does work.

#"Remove 0 Price" = Table.SelectRows(#"Only Previous Month", each ([Price] <> 0)),
#"Remove CONS - JANUARY" = Table.SelectRows(#"Remove 0 Price", each not Text.Contains([Cust PO Number], "JAN") or not Text.Contains([Cust PO Number], "JANUARY")),
#"Remove CONS - FEBRUARY" = Table.SelectRows(#"Remove CONS - JANUARY", each not Text.Contains([Cust PO Number], "FEB") or not Text.Contains([Cust PO Number], "FEBRUARY")),
#"Remove CONS - MARCH" = Table.SelectRows(#"Remove CONS - FEBRUARY", each not Text.Contains([Cust PO Number], "MAR") or not Text.Contains([Cust PO Number], "MARCH")),
#"Remove CONS - APRIL" = Table.SelectRows(#"Remove CONS - MARCH", each not Text.Contains([Cust PO Number], "APR") or not Text.Contains([Cust PO Number], "APRIL")),
#"Remove CONS - MAY" = Table.SelectRows(#"Remove CONS - APRIL", each not Text.Contains([Cust PO Number], "MAY")),
#"Remove CONS - JUNE" = Table.SelectRows(#"Remove CONS - MAY", each not Text.Contains([Cust PO Number], "JUN") or not Text.Contains([Cust PO Number], "JUNE")),
#"Remove CONS - JULY" = Table.SelectRows(#"Remove CONS - JUNE", each not Text.Contains([Cust PO Number], "JUL") or not Text.Contains([Cust PO Number], "JULY")),
#"Remove CONS - AUGUST" = Table.SelectRows(#"Remove CONS - JULY", each not Text.Contains([Cust PO Number], "AUG") or not Text.Contains([Cust PO Number], "AUGUST")),
#"Remove CONS - SEPTEMBER" = Table.SelectRows(#"Remove CONS - AUGUST", each not Text.Contains([Cust PO Number], "SEPT") or not Text.Contains([Cust PO Number], "SEPTEMBER")),
#"Remove CONS - OCTOBER" = Table.SelectRows(#"Remove CONS - SEPTEMBER", each not Text.Contains([Cust PO Number], "OCT") or not Text.Contains([Cust PO Number], "OCTOBER")),
#"Remove CONS - NOVEMBER" = Table.SelectRows(#"Remove CONS - OCTOBER", each not Text.Contains([Cust PO Number], "NOV") or not Text.Contains([Cust PO Number], "NOVEMBER")),
#"Remove CONS - DECEMBER" = Table.SelectRows(#"Remove CONS - NOVEMBER", each not Text.Contains([Cust PO Number], "DEC") or not Text.Contains([Cust PO Number], "DECEMBER"))
in
#"Remove CONS - DECEMBER"

[Rich_Wyeth]

Thank you, that has resolved the issue.

[wardy912 · Accepted solution]

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.