Forum Discussion

campelliann's avatar
campelliann
Post Patron
3 years ago
Solved

How would you approach this? Text.Contains problem

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.Contain...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    campelliann
    Post Patron
    3 years ago

    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