If (or) text.contains

Power Query
1

Hi All,

I created the following formula in power query conditional column:

= Table.AddColumn(#“Removed Columns3”, “Email Filter”, each if Text.Contains([Contact Email] = “ppd.com” or “hubspot.com” or “aes.com” or “evidera.com” or “kettlercuisine.com”) then “Test Emails” else [Contact Email])

but coming with an error message:

Expression.Error: We cannot convert the value “hubspot.com” to type Logical.
Details:
Value=hubspot.com
Type=[Type]

Can someone help? What am I doing wrong?

Thanks

1 Like
2

I figured it out. I had to bracket each instance I had to add quotes around the table.

= each if Text.Contains([Domain], “ppd.com”) then “Do Not Use”
else if Text.Contains([Domain],“hubspot.com”) then “Do Not Use”
else [Domain])

Thanks

1 Like
3

glad you found the answer, thank you for coming back to the forum to post a solution for others :slight_smile:

4

Hi,

Glad you find the solution but if your criteria is more than two, the following M code will be shorter:

each if List.PositionOf({“ppd.com”,“hubspot.com”},[Domain])>=0 then “Do Not Use” else [Domain]

Instead of {“ppd.com”,“hubspot.com”} part, you can select any list.

5

Also if you want to check the items which contain your list, below M code will work:

if List.Count(Splitter.SplitTextByAnyDelimiter({“ppd.com”,“reza.com”})([Domain]))>1 then “Do Not Use” else [Domain]