Power Quey Issues with Conditional Column

[cottrera]

Hi 

I have a table that contains a column called 'Detail' this is a free text field used when customer call into our call centre.

I have created a Conditional Column  that seaches the 'Detail' column and looks for the following words

KeyWordsVermin

rats

pest

pests

rat

mice

mouse

glis glis

vermin

rodent

rodents

ants

wasps

hornets

nest

bees

bedbugs

pigeons

cockroaches

carpet beetles

earwigs

silverfish

These are all common rodents in the UK.  

Here is an example of my conditional column,

I have tried both upper and lower case text . However the output is showing far too many 'Yes'  where the free text in the 'Details' field clearly do not refer to any of the conditions I have set.

What am I doing wrong?

thanks

Richard

[v-xinruzhu-msft · Accepted solution]

Hi @cottrera 

You can refer to the following solution.

1.Set the key word to a new table (name:Query1) and uppercase them

2.in your original table create a custom column( the detail column is uppercased) and input the following code

let 
_count=Table.RowCount(Table.SelectRows(Query1,(x)=>Text.Contains([Detail],x[KeyWordsVermin])))
in
if _count>0 then "Yes" else null

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3]

Hi @cottrera ,

I know that this is not easy to understand, but add this as Custom Column and it will do the job.

I assume that your column with text to search is called Details as you mentioned.
(If you don't want to ingore CaseSensitivity, just change Comparer.OrdinalIgnoreCase for Comparer.Ordinal or delete whole comparer as 3rd argument of Text.Contains)

List.AnyTrue(
        List.Accumulate(
    {"rats", "pest", "pests", "rat", "mice", "mouse", "glis glis", "vermin", "rodent", "rodents", "ants", "wasps", "hornets", "nest", "bees", "bedbugs", "pigeons", "cockroaches", "carpet beetles", "earwigs", "silverfish"},
    {},
    (s,c)=> 
           s & {Text.Contains([Details], c, Comparer.OrdinalIgnoreCase)}
	)
)

 You can find a lot of usefull info about List.Accumulate here.

[cottrera]

Thank you for your quick reponse. The 'Details' field is all Uppercase. And the Condition is also using Upper case.
That said I have added Ordinal.IgnoreCase. This caused and error so I manually typed and the telesense would only show this function Comparer.OrdinalIgnoreCase.
The output is still showing details that do not contain any of the key words I have stated.

Regards
Richard

[watkinnc]

Remember that Power Query is case sensitive to the fullest. When you close that formula dialog, go up to the formula bar, and add the optional parameter Ordinal.IgnoreCase(I'm assuming that this screenshot will result in Text.Contains, so

each if Text.Contains(TableName, [Column], "Varmint", Ordinal.IgnoreCase) then "Yes" else "No")

Might be easier to make the Vermin list into a one column table, and then add a column to that using each Table.FindText(OtherTable, [Details], _[KeyWordVermin], Ordinal.IgnoreCase)

--Nate