M text.contains with OR logical operator, and non-casesensitive matching?

[iagovar]

Hi, I have a column with site domains like:

• juegos.abc.com
• blogs.abc.com
• reddit.com
• elpais.com

I want to crate a conditional column, that tells me if a domain is "media" or "community" based on a given list of domains.

I can do it manually from "create conditional column" in PowerBi but it will take me a million years.

Instead I've tried:

"Added Conditional Column18" = Table.AddColumn(#"Added Conditional Column17", "sitio.tipo", each if Text.Contains([Content.thread.site], "elpais OR elmundo OR elconfidencial OR abc OR lavanguardia OR 20minutos OR eldiario OR rtve OR elespanol OR eleconomista OR publico OR telecinco OR lasexta OR cuatro") then "medio" else "comunidad" )

Not working.

Also tried:

"Added Conditional Column18" = Table.AddColumn(#"Added Conditional Column17", "sitio.tipo", each if Text.Contains([Content.thread.site], "elpais" OR "elmundo" OR "elconfidencial" OR "abc" OR "lavanguardia" OR "20minutos" OR "eldiario" OR "rtve" OR "elespanol" OR "eleconomista" OR "publico" OR "telecinco" OR "lasexta" OR "cuatro") then "medio" else "comunidad" )

Same problem. I tried to find how to use operators withing the documentation, but it doesn't say much. Just what operators you can use, but I don't really know how to use it within this formula.

I also noticed with other conditional columns that it does precision matching, so, for example, if I specify "ABC" and the cell contains "abc" it won't match. I want it to be non-casesensitive, is it possible?

[v-yulgu-msft · Accepted solution]

Hi @iagovar,

Please try below logical code:

#"Added Conditional Column" = 
Table.AddColumn(#"PreviousStep", "sitio.tipo", 
each if List.AnyTrue(List.Transform({"elpais","elmundo","elconfidencial","abc","lavanguardia","20minutos","eldiario","rtve","elespanol","eleconomista","publico","telecinco","lasexta","cuatro"}
,(substring) => Text.Contains([Content.thread.site], substring,Comparer.OrdinalIgnoreCase))) 
then "medio" else "comunidad" )

Best regards,
Yuliana Gu