Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 f...
Need Help
Tutorial Requests
LivioLanzo's avatar
LivioLanzo
Solution Sage
7 years ago

Anonymous

 

Could you show a visual example?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

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.

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

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

    Sample data:

    Create a distinct table:

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

    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")))

    Create relationships between the two tables:

    Create a calculated column in your row table:

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

    Result:

     

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

     

    Regards,
    Daniel He