Remove rows based on category column

[alexlopesBS]

Hello, guys. 

Need help with some ETL stuff.

I have a table that comes to me bringing the following data:

Store	Product Department	Store Category	Values
1	Alpha	A	12345
1	Beta	A	12345
2	Alpha	B	12345
2	Beta	B	12345
2	Caos	B	12345
3	Alpha	C	12345
3	Beta	C	12345

I get this info from a cube that cotains data on all departments for every store, and put it on excel. I cannot do anything with the data before it comes to excel. 

I then add it to Power BI and do the ETL process to fix it up. 

However, the client is requesting that I filter out "dead" departments in each store category, because it is tainting the whole picture with bad numbers. 

How could I do something like this:

DELETE ROW WHERE [Store Category] = B AND [Product Department] IN ("Beta", "Caos")

with Power Query? I am trying to figure it out using the documentation, but I am having a lot of difficulty finding the basic functions for Power Query on the microsoft site.

[Dwivedi15]

This did filtering the table but when counting rows using measure, it is counting all the rows even those filtered out rows.

[MarcelBeug · Accepted solution]

The easiest way is to reformulate your selection criteria to select rows where [Store Category] <> "B" or ([Product Department] <> "Beta" and [Product Department] <> "Caos").

First select [Store Category] <> "B" to create base code:

then adjust the generated code (in the red rectangle in the picture) to:

= Table.SelectRows(#"Changed Type", each [Store Category] <> "B" or ([Product Department] <> "Beta" and [Product Department] <> "Caos"))

[jaynesa]

How do you delete the rows you don't want?