Create column with "Like in SQL"

[anandprakashnm]

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 · Accepted solution]

@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]

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.

[KGrice]

Hi @anandprakashnm. Have you tried the SEARCH function in DAX? That allows you to use wildcards. You might also find this post from the community helpful.