Remove/Filter out rows based on values in 2 columns

[Anonymous]

I am working with sales pipeline data from Salesforce. The two columns I need to filter with are Stage and Opportunity Office. These columns contain the following:

Stage: Target, Prospect, Proposal / Price Quote, Negotiation / Review, Closed Won, Closed Lost

Opportunity Office: Contains 57 distinct Office names.

Due to confidentiality reasons, I need to exclude/filter out rows if the following conditions are met:

1. [Opportunity Office] = "Wealth Advisors" & [Stage] = "Target", "Prospect", "Proposal / Price Quote", "Negotiation / Review"

2. [Opportunity Office] = "Transaction Services" & [Stage] = "Target", "Prospect", "Proposal / Price Quote", "Negotiation / Review"

Essentially, if the Opportunity Office is Wealth Advisors or Transaction Services, I only want to keep the rows where Stage is Closed Won or Closed Lost.

I believe I need to have separate steps to keep the needed stages for each of the 2 offices. I've tried the following, starting with the Wealth Advisors Office: 

= Table.SelectRows(#"Replaced Introhive w Null", each ([Opportunity Office] = "Wealth Advisors") and ([Stage] <> "Target" and [Stage] <> "Prospect" and [Stage] <> "Proposal / Price Quote" and [Stage] <> "Negotiation / Review"))

This code successfully leaves me with only Wealth Advisor Opportunities in the Closed Won and Closed Lost stages, but I also need the Opportunities for all other Opportunity Offices to remain in the table.

I've attempted the method to add a new column that states "true" if the conditions are met and "false" if the conditions are not met. That way I can remove the "true" rows. The following code attempts for added column resulted in "false" for every row:

if ([Opportunity Office] = "Wealth Advisors") and ([Stage] = "Target" and [Stage] = "Prospect" and [Stage] = "Proposal / Price Quote" and [Stage] = "Negotiation / Review") then 1 else 0

= Table.AddColumn(#"Replaced Introhive w Null", "WA Bad Opps", each List.ContainsAll({[Opportunity Office], [Stage]}, {"Wealth Advisors", "Target", "Prospect", "Proposal / Price Quote", "Negotiation / Review"}))

= Table.AddColumn(#"Replaced Introhive w Null", "WA Bad Opps", each if ([Opportunity Office] = "Wealth Advisors" and [Stage] = "Target" and [Stage] = "Prospect" and [Stage] = "Proposal / Price Quote" and [Stage] = "Negotiation / Review") then 1 else 0)

Do I need to use Table.ContainsAll rather than List.ContainsAll? What's the most efficient way to accomplish this?

[DataInsights · Accepted solution]

@Anonymous,

Try this custom column in Power Query:

if List.Contains({"Wealth Advisors", "Transaction Services"}, [Opportunity Office]) and List.Contains({"Closed Won", "Closed Lost"}, [Stage]) then 1 else 0

[Anonymous]

@DataInsights , This worked like a charm, with a tweak to the list items. When I used the exact code, it marked the opportunities I wanted to keep in the table rather than remove. With the below code, only the rows meeting the criteria ended up with a 1 and I was able to filter out those rows.

if List.Contains({"Wealth Advisors", "Transaction Services"}, [Opportunity Office]) and List.Contains({"Target", "Prospect", "Proposal / Price Quote", "Negotiation / Review"}, [Stage]) then 1 else 0

Thanks so much!!