Filter out multiple values from a list

[Anonymous]

Hello,

I have a table that containts a list of all contacts that I have exported from our CRM (salesforce.com) that I want to cleanup.

I am looking to filter the list to remove all contacts that have a an email from a public domain (gmail, hotmail, yahoo, ect.) and keep only the ones with company emails.

I don't want to use a slicer and go trought the list of emails manually as there are over 15,000 contacts in that list.

I managed to exclude one domain by using the page level/report level filters and keeping only the records for which the email adress did no contain "@gmail" but I can only do it for one so I am left with all the other public domains.

I also tried creating a new column by splitting the email column using the "@" as delimiter and then using a slicer. This reduced the size of the list but there are still over 500 different domains to pick from so this option is far from ideal.

Does anyone have a idea on how to filter out/exclude multiple values at a time?

Thanks in advance,

Phil

[QueryWhiz]

Hi. As a workaround, maybe you can try to test your connection with a 3rd party connector instead of manually exporting data from SF. I currently use Windsor.ai and I can directly pull contacts data from SF and I can even exclude some values directly on Windsor interface using the filters option. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[v-shex-msft]

HI @Anonymous ,
Maybe you can consider to add a public/company domain list to your report, then filter records based on public/company domain list.
In addition, I'd like some sample data to help us clarify your data structure for test and coding formula.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

Please find a short example of my data below, note that contact information is fake and not valid.

Table Name: Contact

First Name	Last Name	Email	Phone	job title	Company
John	Doe	jdoe@sony.com	555-555-5555	Director of Marketing	Sony
Jenny	Brooks	jennyb@samsung.com
Peter	Parker	peter@gmail.com	555-555-5555	Consultant	Peter Inc.
Susan	James	sj101@hotmail.com	555-555-5555	Student
Kim	Sullivan	Kim.Sullivan@hp.com		Analyst	HP
Brenda	Wozniak	WozniakB@lexus.com	555-555-5555		Lexus
Scott	Wade	Scott.wade@ups.com	555-555-5555	VP Customer services	UPS
Natalia	hernandez	NatCat@outlook.com	555-555-5555
Alex	Rawlings	AlexRaw@aol		Customer support agent	Ford
Patrick	Kane	patrick.kane@verizon.com	CX Director	Verizon

As with any other contact database not all fields are populated with prevents me from filtering on other values like company or title. The only required field to enter a new record in the table was an email address so filtering on the email seems like the only way to properly clean that database.

Ultimately, I want to get a table with excluding all record with a public email

Also, since in some rare occasions some public email might be relevant if other criterias are meet, I would prefer to apply the filter at the report level and not at the initial query level when importing the data from salesforce.com

I hope this can help you better understand what I am trying to get to and define the right query/measure

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

As I said, you can add public domain table with domain text which you want to exclude.

Then add a calculated column to contact table to compare current row content with public domain table list.

Is Public = 
COUNTROWS (
    FILTER (
        ALL ( 'Pubich Domain' ),
        SEARCH( 'Pubich Domain'[Domain], [Email], 1, -1 ) > 0
    )
) > 0

Regards,

Xiaoxin Sheng

[Anonymous]

why dont u apply filter in query editor where email domain = your company...n ignore all other...

[Anonymous]

Hello Reddy_powerBi

My list contains customer contacts so they all have different domains so what you propose is not a viable option in that case

Thanks,