I have a column with site domains like:
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?
Text.Contains takes a third argument which tells it how to do comparisons. If you want to ignore the case, use Comparer.OrdinalIgnoreCase, like Text.Contains([column], "Text", Comparer.OrdinalIgnoreCase).
That being said, Text.Contains will only check if the exact text value you pass into the second parameter is in the first parameter. If you want to check that the text value contains any value in a list, you'll need to use something like List.Contains, which also takes in a comparer as its third argument.
This should do what you want:
"Added Conditional Column18" = Table.AddColumn(#"Added Conditional Column17", "sitio.tipo", each if List.Contains({"elpais", "elmundo", "elconfidencial", "abc", "lavanguardia", "20minutos", "eldiario", "rtve", "elespanol", "eleconomista", "publico", "telecinco", "lasexta", "cuatro"}, [Content.thread.site], Comparer.OrdinalIgnoreCase) then "medio" else "comunidad")
You can make this simpler if you have the list of domains in another table column. In that case replace the list in the first parameter with the reference to a column, like Query_Name[column_name].