Forum Discussion
Remove/Filter out rows based on values in 2 columns
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 ...
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
DataInsights
Super User
Super UserAnonymous,
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
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!!