Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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.
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.
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.
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.