Conditional Filtering based on another column value

[Anonymous]

I have some data that has bad values and I am trying to filter out values not in a range. The issue I am running into is that ther are more than 1 range the values can be in depending on the product. I tried writing an if statement that will filter the column but I keep running into errors.

First I get this error:

"Expression.SyntaxError: Token Else expected."

Then after adding an 'else' I get:

"Expression.SyntaxError: Token Literal expected." or "Expression.Error: We cannot convert a value of type Function to type Logical."

This is what I have so far:

= Table.SelectRows(#"Changed Type1", each if [Spec Denier] = 1385 or 1411 or 1426 then each [Denier] = null or ([Denier] >= 1300 and [Denier] <= 1500) else if [Spec Denier] = 1700 then each [Denier] = null or ([Denier] >= 1600 and [Denier] <= 1800))

 I have tried removing the 'or' in the if statement and adding an 'else' at the end. All I want to do is keep the blank data and make sure that the data is in the range for that product.

[Anonymous]

The data that I have is manually input and sometimes there are typos. I want to filter them in a range based on the denier it is supposed to be (ie. 1411 or 1700). Here is example data. The quality column shows if the data should be kept or filtered out.

Date	Machine #	Denier	quality
12/4/2022	52	1420	good
12/4/2022	52	1435	good
12/4/2022	52	1685	BAD
12/4/2022	52	1413	good
12/4/2022	52	1395	good
12/4/2022	52	1435	good
12/4/2022	52	1437	good
12/4/2022	52	1402	good
12/4/2022	52	1444	good
12/4/2022	52	1285	BAD
12/4/2022	52	1444	good
12/4/2022	52	1395	good
12/4/2022	52	1435	good
12/4/2022	52	1436	good
12/4/2022	52	1416	good
12/4/2022	52	1450	good
12/5/2022	61	1682	good
12/5/2022	61	1411	BAD
12/5/2022	61	1721	good
12/5/2022	61	1683	good
12/5/2022	61	1742	good
12/5/2022	61	1751	good
12/5/2022	61	1680	good
12/5/2022	61	1727	good
12/5/2022	61	1712	good
12/5/2022	61	1729	good
12/5/2022	61	11700	BAD
12/5/2022	61	1699	good
12/5/2022	61	1697	good

[v-jingzhang]

Hi @Anonymous 

Which rows do you want to keep?

If you want to keep all quality Good rows, you can click the down icon on "quality" column header and select "good" value. 

If you want to add more filter conditions, you can double click the "Filtered Rows" step in "Applied Steps" pane. This will open a "Filter Rows" window. Switch to "Advanced" option, then you are able to add multiple conditions. After clicking "ok", it will generate an M statement for you automatically. 

To filter several ranges based on different machines, you can try something like below.

= Table.SelectRows(#"Changed Type", each ([#"Machine #"] = 52 and ([Denier] >=1400 and [Denier] <=1500)) or ([#"Machine #"] = 61 and ([Denier] =11700 or [Denier] <=1500)))

Hope above examples help. If it doesn't help, can you give a detailed example that which range you want to keep or filter out based on the current data?

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

[v-jingzhang]

Hi @Anonymous 

The syntax of your IF statement is not correct, so you have those errors. Please share some sample data to help us understand your expected result better. You can use natural language or examples to explain the logic for filtering. At present we have difficulty understanding your requirement from the incorrect IF statement. 

Best Regards,
Community Support Team _ Jing

[Mahesh0016]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.