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([...
  • 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

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.