Forum Discussion
Solved
Power Query: Apply Filter Expression Only if Condition Met in Another Column
I have the following filter expression: = Table.SelectRows(#"Expanded All", each ([Column1] >= [Column4] and [Column1] <= [Column5]) or ([Column2] >= [Column4] and [Column2] <= [Column5])
) ...
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] )
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]
)