To search text from column to create customized column

[Anonymous]

I have column ( Message) of text data type, I am trying to create customer column (Rating) using it :

logic :

IF CONTAINS([Message],'5') OR CONTAINS([Message],'Very Easy') THEN
'5: Very Easy'
ELSEIF CONTAINS([Message],'4') OR CONTAINS([Message],'Easy') THEN
'4: Easy'
ELSEIF CONTAINS([Message],'3') OR CONTAINS([Message],'Neither Easy or Hard') THEN
'3: Neither Easy or Hard'
ELSEIF CONTAINS([Message],'1') OR CONTAINS([Message],'Very Hard') THEN
'1: Very Hard'
ELSEIF CONTAINS([Message],'2') OR CONTAINS([Message],'Hard') THEN
'2: Hard'
ELSE
'EXCEPTIONS'
END

Can somebody please help?

[az38 · Accepted solution]

Hi @Anonymous 

try column

Column = 
SWITCH(
TRUE(),
OR(FIND('5',[Message],-1)>0;FIND('Very Easy',[Message],-1)>0),'5: Very Easy',
OR(FIND('4',[Message],-1)>0;FIND('Easy',[Message],-1)>0),'4: Easy',
OR(FIND('3',[Message],-1)>0;FIND('Neither Easy or Hard',[Message],-1)>0),'3: Neither Easy or Hard',
OR(FIND('1',[Message],-1)>0;FIND('Very Hard',[Message],-1)>0),'1: Very Hard',
OR(FIND('2',[Message],-1)>0;FIND('Hard',[Message],-1)>0),'2: Hard',
'EXCEPTIONS'
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can add a custom column in Power Query Editor To meet your requirement:

if Text.Contains([Message] , "3") or Text.Contains([Message], "Neither Easy or Hard") then "3: Neither Easy or Hard" 
else if Text.Contains([Message] , "5") or Text.Contains([Message], "Very Easy") then "5: Very Easy" 
else if Text.Contains([Message] , "4") or Text.Contains([Message], "Easy") then "4: Easy" 
else if Text.Contains([Message] , "1") or Text.Contains([Message], "Very Hard") then "1: Very Hard" 
else if Text.Contains([Message] , "2") or Text.Contains([Message], "Hard") then "2: Hard" 
else "EXCEPTIONS"

We can also create a calculated column using following dax to meet your requirement:

Column = 
IF (
    SEARCH ( "3", [Message],, -1 ) > 0
        || SEARCH ( "Neither Easy or Hard", [Message],, -1 ) > 0,
    "3: Neither Easy or Hard",
    IF (
        SEARCH ( "5", [Message],, -1 ) > 0
            || SEARCH ( "Very Easy", [Message],, -1 ) > 0,
        "5: Very Easy",
        IF (
            SEARCH ( "4", [Message],, -1 ) > 0
                || SEARCH ( "Easy", [Message],, -1 ) > 0,
            "4: Easy",
            IF (
                SEARCH ( "1", [Message],, -1 ) > 0
                    || SEARCH ( "Very Hard", [Message],, -1 ) > 0,
                "1: Very Hard",
                IF (
                    SEARCH ( "2", [Message],, -1 ) > 0
                        || SEARCH ( "Hard", [Message],, -1 ) > 0,
                    "2: Hard",
                    "EXCEPTIONS"
                )
            )
        )
    )
)

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY7NCgIxDIRfpfRcFvx7BMGTRy9LD8FkcU7Brqjr05ukhR4yM/kynecMQjolkEKFci3mTHyTtqUzrZsC2l0+QrmPZIyHRmEgBxBhhGrvKng9pMWNpC1dqHHEey+yhRDuDj3oUnbdCCWIj/iaemmnnh9pvWrlJYbf9/7Otf4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Message = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Message", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Message] , "3") or Text.Contains([Message], "Neither Easy or Hard") then "3: Neither Easy or Hard" 
else if Text.Contains([Message] , "5") or Text.Contains([Message], "Very Easy") then "5: Very Easy" 
else if Text.Contains([Message] , "4") or Text.Contains([Message], "Easy") then "4: Easy" 
else if Text.Contains([Message] , "1") or Text.Contains([Message], "Very Hard") then "1: Very Hard" 
else if Text.Contains([Message] , "2") or Text.Contains([Message], "Hard") then "2: Hard" 
else "EXCEPTIONS")
in
    #"Added Custom"

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

[az38]

oh, pardon

my solution was for DAX 😞

[az38 · Accepted solution]

Hi @Anonymous 

try column

Column = 
SWITCH(
TRUE(),
OR(FIND('5',[Message],-1)>0;FIND('Very Easy',[Message],-1)>0),'5: Very Easy',
OR(FIND('4',[Message],-1)>0;FIND('Easy',[Message],-1)>0),'4: Easy',
OR(FIND('3',[Message],-1)>0;FIND('Neither Easy or Hard',[Message],-1)>0),'3: Neither Easy or Hard',
OR(FIND('1',[Message],-1)>0;FIND('Very Hard',[Message],-1)>0),'1: Very Hard',
OR(FIND('2',[Message],-1)>0;FIND('Hard',[Message],-1)>0),'2: Hard',
'EXCEPTIONS'
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn