Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
Proud to be a Super User!
@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
Proud to be a Super User!
@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!!
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |