Unable to remove keywords from the text body field

[jssonali]

Hi, 
Please assist, I want to exclude the rows that have the below keywords from text body field. The text body is text datatype. Currently it removes the rows where the text is written in same way otherwise it shows up if used in Captial or other way.  My data source is from Salesforce. 

"Keywords : please close at your end, have been granted, out of office, Many thanks, much appreciated, thankyou, I have unblocked your account, close the case, resolved, I have successfully logged in., User unblocked. , Update done in Salesforce, .The request is now complete, User is unblocked, Access confirmed.Thank you for your quick resolution, I appreciate it, I now have access, Please close the ticket,  Access is given"

Currently I tried to use the Add Conditional Column as below that removes the keywords from text body and returns the rows with data that are useful. 

Is there any other way you could help how to remove these keywords. Looking forward for steps.

[QueryWhiz]

Hi @jssonali As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the SF objects API and would let you filter out these records in the tool UI diretly before exporting the data to PBI. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. 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. 

[_AAndrade]

Show me the code and some examples or share a pbix file with same data to test it, if it's possible to you

[_AAndrade]

Hi @jssonali,

Did it solve your problem?
Did my message help you in any way? If it did, please mark my message as the solution and give it a "kudo", I'll appreciate it and help me out

[_AAndrade]

Why?

[_AAndrade]

Hi,

I did a simple example using the formula below and it's working:

M formula:

Table.AddColumn(#"Changed Type", "DeleteRow", each
 if List.Contains({"please close at your end", "have been granted","out of office", "many thanks","much appreciated"  },Text.Lower([Text Body])) 
then "YES" else "NO")

For your example, you have to put all your keywords in List.Contains in lower case and it should work correctly.
If you need any help please let me know.

[jssonali]

It doesn't work for me.

[jssonali]

It is just displaying me No and still the textbody contains the keywords.