How to group/combine row definition into a specific category

[Anonymous]

Hello Experts,

I need to group/combine row variables into a specific category type. For example;

I have a column called industry category where I have row variables captured as "Supermarket/Supermarkets/SuperMkts" for three different businesses. But they are part of the same industry type. I would like to group them as "Supermarkets" so that I can visualise my data in the correct format. Now I can group them by right-clicking on the column and select "New Group". It's a cumbersome task when I need to do a similar exercise for 20k line items. I was wondering if there is a way to automate this process by writing a dax code or in power query! 

I am unable to find a solution. It would be great if you could help me with this.

Thanks in advance

Regards

ronie

[gvrajesh]

Hi @Anonymous 

You can create a calcualted column like below

[Anonymous]

Hello @gvrajesh,

Thank you for your reply. I can certainly apply your logic to search a specific keyword. Unfortunately, I have plenty to look for. Can I add multiple search rules in single containsstring function? I was thinking of this way; 

CONTAINSSTRING('table name1'[column1],"Accomodation") || CONTAINSSTRING('table name1'[column1],"Apartments"), "Accomodation")

I need to add few more keywords. Is there a better way to implement it?

Thanks

[gvrajesh · Accepted solution]

Hi @Anonymous 

Bestway is create lookup table like below  and create calculated column

In future if you want to add new values, you can simply update this lookup table.

Create calculate column