Forum Discussion

anandprakashnm's avatar
anandprakashnm
Frequent Visitor
10 years ago
Solved

Create column with "Like in SQL"

Hello,

 

I am trying to group some of the data with a new column identifier and i have a lot of identifier. I want to use something like this:-

 

new column = if([column1] like "B%", "Ball", if([column1] like "A%", "Apple","Others"))

 

i have a lot of data that its impossible to type all data in the if statement.

 

Please help!!

Regards,

Anand Prakash

  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    anandprakashnm

     

    As KGrice said, you can create a new column by using the SEARCH function like below. BTW, the search function is case insensitive.

    Column = 
IF (
    SEARCH ( "B*", Table1[column1],, 0 ) = 0,
    IF ( SEARCH ( "A*", Table1[column1],, 0 ) = 0, "Others", "Apple" ),
    "Ball"
)

     

    Best Regards,

    Herbert

3 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    anandprakashnm

     

    As KGrice said, you can create a new column by using the SEARCH function like below. BTW, the search function is case insensitive.

    Column = 
IF (
    SEARCH ( "B*", Table1[column1],, 0 ) = 0,
    IF ( SEARCH ( "A*", Table1[column1],, 0 ) = 0, "Others", "Apple" ),
    "Ball"
)

     

    Best Regards,

    Herbert

    • ImkeF's avatar
      ImkeF
      Community Champion
      10 years ago

      If the number of characters proceeding your % is variable, this task is actually not so easy (imagine how you would like to deal with "air" and "chair" for example). With fixed number of characters, you could use Text.Start("YourText", NumberOfCharacters) and then perform a lookup to your lookup-table.

       

      But if it's variable, you could use Text.StartsWith to check if the text starts with any given string from your lookup-table. You need to use a function in order to iterate through the lookup-table and return the lookup-value if true.