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
)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?