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.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

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.

6 Replies

    • campelliann's avatar
      campelliann
      Post Patron
      3 years ago

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

  • 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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    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.