Conditional Filter in PowerQuery

[Anonymous]

Category	Subcategory	Filter
A	A1	ok
A	A2	ok
A	Aa	remove
A	Ab	remove
B	Ba	ok
B	Bb	ok
C	C1	ok

I have this table and I want to find a way to implement the following logic: Filter out the lines with subcategory letter letter (Aa) if there is at least 1 instance of subcategory letter number (A1), if there is not, keep them (like Ba or Bb)

Is there any way to do it in Power Querry?

[Anonymous · Accepted solution]

Hi @Anonymous 

Firstly use Group by function as below.

Add Custom columns by M code:

Has A1 or not
=
if Table.Contains([Rows],[Subcategory = "A1"]) then 1 else 0

Then Expand Rows by Subcategory column.

Add Custom columns to get result.

Filter
=
if [Has A1 or not]=1 and [Second word is letter or not] = 0 then "Remove" else "OK"

Result is as below.

Then you can filter your table by Filter column, only keep rows with "OK" and remove columns you don't need.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.