cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iagovar
Regular 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
v-yulgu-msft
Microsoft
Microsoft

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.

View solution in original post

1 REPLY 1
v-yulgu-msft
Microsoft
Microsoft

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.

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors