Forum Discussion
Power Query: Apply Filter Expression Only if Condition Met in Another Column
The next step is to rearrange the query so that the most frequently encountered condition is tested for first etc.
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] )
concatenate both filters -
= Table.SelectRows(#"Expanded All", each [Column1] >= [Column4] and [Column1] <= [Column5]) or ([Column2] >= [Column4] and [Column2] <= [Column5] and [Column3]>1
)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.
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 )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.
The next step is to rearrange the query so that the most frequently encountered condition is tested for first etc.