cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iagovar Visitor
Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

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

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

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.