Power Query: Apply Filter Expression Only if Condition Met in Another Column

[MJEnnis]

I have the following filter expression: 

= Table.SelectRows(#"Expanded All", each ([Column1] >= [Column4] and [Column1] <= [Column5]) or ([Column2] >= [Column4] and [Column2] <= [Column5])
)

That works fine. But I want to apply it only 

if [Column3] > 1

Column3 counts eroneous duplicate rows after a Left Outer Join, and this is the only logic I can find to not remove rows without duplicates that I need to keep.

Anyone know a way to add the if statement to the filter expresion?

Thanks

[lbendlin · Accepted solution]

The next step is to rearrange the query so that the most frequently encountered condition is tested for first etc.

[MJEnnis · Accepted solution]

There turned out to be some unexpected instances of duplicatation where both range statements were true. (errors in the data) So the final version that worked was: 

= Table.SelectRows(#"Expanded All", each 
    [Column3] = 1 or 
    [Column1] >= [Column4] and [Column1] <= [Column5] or 
     not ([Column1] >= [Column4] and [Column1] <= [Column5]) and 
        [Column2] >= [Column4] and [Column2] <= [Column5]
)

[MJEnnis]

@lbendlin 

Below was my prompt for ChatGPT. Maybe I explained what I want more clearly for AI, because I know how "artificial" it can be. 😄 Will your solution accomplish what I describe below?

I have the following expression to filter rows based on a column:

= Table.SelectRows(#"Expanded All", each ([Column1] >= [Column4] and [Column1] <= [Column5]) or ([Column2] >= [Column4] and [Column2] <= [Column5])
)

But I only want to apply the filter if a condition is met in a column currently not referenced in the expression. I want to keep all rows that do not meet the condition, and I want to apply the above filter only to rows that meet that contion.

This is the condition:

if [Column3] > 1

Is there a way to add this condition to the above expression to achieve my goal?

[lbendlin]

There is some friction between boolean language and natural language.

When you say "I want A and B from column C"  then the boolean equivalent is   C= "A" OR C = "B".

Like in algebra there is an order of execution. AND executes before OR.  if you don't like that you need to protect the OR with parentheses.  You don't need to protect the AND, but extra parentheses (while useless) don't hurt.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MJEnnis]

Wow! I never use AI for coding purposes. But I am in a pinch here, and Chat GPT just gave me this solution:

= Table.SelectRows(
    #"Expanded All",
    each 
        ([Column3] <= 1) or 
        (
            ([Column1] >= [Column4] and [Column1] <= [Column5]) or
            ([Column2] >= [Column4] and [Column2] <= [Column5])
        )
)

Will be impressed if that works!

[lbendlin]

That AI answer is, uhm, not very correct.

[lbendlin]

concatenate both filters -

= Table.SelectRows(#"Expanded All", each [Column1] >= [Column4] and [Column1] <= [Column5]) or ([Column2] >= [Column4] and [Column2] <= [Column5] and [Column3]>1
)

[MJEnnis]

Based on what you said above about the order of operations for M, I think this is what I actually want:

= Table.SelectRows(#"Expanded All", each [Column1] >= [Column4] and [Column1] <= [Column5] or [Column2] >= [Column4] and [Column2] <= [Column5] or [Column3]=1
)

But this is basically the equivalent of what ChatGPT produced, just without the unnecessary parentheses.

My only doubt is that I want to be sure that [Column3]=1 is prioritized. I want to keep all rows where column 3 = 1, even if the other conditions would filter them out.

[lbendlin]

You have a second "or"  statement there that is unprotected. Is that what you want? 

Table.SelectRows(#"Expanded All", each 

[Column1] >= [Column4] and [Column1] <= [Column5] 
or [Column2] >= [Column4] and [Column2] <= [Column5] 
or [Column3]=1

)

[MJEnnis]

Yes, I believe so. 

I want to keep ALL rows where [Column3] = 1 by default. Because those are rows that were not erroneously duplicated by a Merge.

For the rows where [Column3] > 1, I want to apply the other two expressions to filter out the erroneous duplicates that do not meet those conditions: 

[Column1] >= [Column4] and [Column1] <= [Column5]

OR

[Column2] >= [Column4] and [Column2] <= [Column5].

There are some instances where both [Column1] and [Column2] fall between the indicated range, but they should be caught by both expressions.

[lbendlin · Accepted solution]

The next step is to rearrange the query so that the most frequently encountered condition is tested for first etc.

[MJEnnis]

Would this use computing resources more efficiently?

[lbendlin]

Yes, it would minimize the compute cycles.

[MJEnnis]

Hi @lbendlin! You have helped me several times in the past, and I definitely trust you over AI! Two questions: 

1) I do not understand the placement of the parentheses. For example, should there be another parenthesis before the first instance of [Column1]?
2) Why do you add the new condition statement onto the end of the second filter option? Shouldn't it be separated by parenthesis?
3) The one ChatGPT gives me makes sense to me. But I am not familiar enough with M to undertand yours. Is your solution more efficient?