Need to Group field values to create custom column

[Anupal]

Hello,

I am looking to creata a customer column by grouping values of that column and creating a new field. Below is what I am trying to get-

Current Values in the field		Expected Values in the field
Country		Country_CUSTOM
USA		USA
JAPAN		JAPAN
CHINA		Others
INDIA
UK

I have a field called Country which has 5 values. I want to create a custom field with 2 values(USA and JAPAN) as is and club remaining 3 as OTHERS.

I would appreciate the suggestions on how to get this.

TIA

AP.

[talespin]

hi @Anupal 

There are many ways to group in power BI

Prefer Power Query

Otherwise, have you tried Power BI DM > Data Group?

[jgeddes]

A calculated column with the following should work.

Country_CUSTOM =
SWITCH(
TRUE(),
OR([Country] = "USA", [Country] = "Japan"), [Country],
"OTHERS"
)

[Anupal]

Hello @jgeddes , thanks for the solution,.

However I have a question, does the above logic works only on 2 values?

For Eg: If I have to show 3 countries (USA,JAPAN,CHINA) and rest 2 countries as OTHERS..then  also the same will work?

[jgeddes · Accepted solution]

In that case your calculated column should look like this...

Country_CUSTOM_2 = 
SWITCH(
TRUE(),
[Country] IN {"USA", "JAPAN", "CHINA"}, [Country],
"OTHERS"
)

In this version you can add countries to the list as you wish and all other countries not listed will be "Others".

[Anupal]

Thanks @jgeddes , It worked. 🙂

Thanks Again.