cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SeanKH19 Frequent Visitor
Frequent Visitor

Create a Custom Column That Categorizes Data from Another Column

Hi,

 

I have a column of data with about 200 distinct values over thousands of rows. I'd like to create a column that assigns one of about 20 different categories to the values. The only way I've found to do this is to create a conditional column that has a new rule for each of the 200 distinct values. This is very inefficient. Is there a better way to do this?

 

Thanks for your help!

 

4 REPLIES 4
Super User
Super User

Re: Create a Custom Column That Categorizes Data from Another Column

@SeanKH19

 

Could you show a visual example?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Community Support Team
Community Support Team

Re: Create a Custom Column That Categorizes Data from Another Column

Hi @SeanKH19,

Could you please offer a sample data and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
SeanKH19 Frequent Visitor
Frequent Visitor

Re: Create a Custom Column That Categorizes Data from Another Column

Fruit Table.png

 

For example, if I had a table like this, with the first two columns, but I wanted to create the third column, categorizing the values in the first into about 20 different categories. But imagine that the first column has about 200+ distinct values over thousands of rows.

Unfortunately this can't just be offloaded onto someone else as a data management issue.

Community Support Team
Community Support Team

Re: Create a Custom Column That Categorizes Data from Another Column

Hi @SeanKH19,

Based on my test, you could refer to below steps:

Sample data:

1.PNG

Create a distinct table:

Distinct table = DISTINCT('Table1'[Item])

1.PNG

Create a calculate column in this table:

Type = IF('Distinct table'[Item]="Apples"||'Distinct table'[Item]="Oranges"||'Distinct table'[Item]="Grapes","Fruit",
        IF('Distinct table'[Item]="Beef"||'Distinct table'[Item]="Chicken","Meat",
        IF('Distinct table'[Item]="Potatoes","Vegetable","Nuts")))

1.PNG

Create relationships between the two tables:

1.PNG

Create a calculated column in your row table:

Column = RELATED('Distinct table'[Type])

Result:

1.PNG

 

You could also download the pbix file to have a view.

 

Regards,
Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.