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.

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.