How would you approach this? Text.Contains problem

[campelliann]

Hi,

I have to categorize projects based on one word. If the project Name contains "Formation" (as in training), I consider the project as a training type. The problem is that if I use Text.Contains(column, "Formation") I could be considering a project with words like "information" in the wrong category.

How do I solve this?. Do I need to use a Text.Split and lists or something? I am afraid this would hurt performance...

Many thanks and best wishes

[v-yalanwu-msft · Accepted solution]

Hi, @campelliann ;

You could create a custom column as follow:

=if Text.Contains( Text.Upper([Project]),"INFORMAÇÃO") then "Not"
else if  Text.Contains( Text.Upper([Project]),"FORMAÇÃO")
then "Traing"
else "Null"

The final show:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcssvyk08vPzw4nyFzDyF/NKS4sriktTcYqVYnWilNBTJrMSyRLCwe2pxCUgsJVHBMw+hRj8TiaMUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.Contains( Text.Upper([Project]),"INFORMAÇÃO") then "Not"
else if  Text.Contains( Text.Upper([Project]),"FORMAÇÃO")
then "Traing"
else "Null")
in
    #"Added Custom"

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

[Daryl-Lynch-Bzy]

Hi @campelliann - I need more information about your requirement to help because the example provided would work in Power Query because it default match is Case Sensitive.  

[campelliann]

Hi there mate. I need to do a report on the amount of hours we dedicate to training which in portuguese is "Formação". We also have for instance "informação" for Information.  So lets say we have training in outsystems or java. The project Names could come about this way:
Project: Formação in outsystems -- it should be considered as Training

Project: formação in java -- it should be considered as training

Project: Gestão da Informação/informação its should NOT be considered

[Daryl-Lynch-Bzy]

How about using Text.Proper - PowerQuery M | Microsoft Learn before the search - could this work?

[serpiva64]

Hi,

use Text.StartWith instead of Text.Contain

[campelliann]

Hi and thank you for your input. The issue is that the word often comes in the middle of the text...