How do I create a new table with distinct values from an existing table?

[RFJammer]

Hi, hoping someone can help. 

I need to create a new table which contains the distinct values from another table. I know how to do this using when creating a new table in the table view, but (unless I am missing something) I am then not able to add a new column and manually type in values. 

Effectively what we have is a student table, and part of each record identifies the student's ethnicity. Unfortunately, because of the way the data has been entered, there are lots of inconsistencies; e.g. White British, White British Welsh, White Scottish, British White etc. So what I want to do is pull the unique values from this table into a new table and then manually add a new column and type in a new value to link that nationality to a group; e.g., all the examples I gave would be part of the 'White British' group. 

I assume I need to do this in the transform data window, but am not sure how to go about it. Can anyone point me in the right direction?

[Anonymous]

Hi @RFJammer ,

Type

White British

White British Welsh

White British Welsh

White Scottish

White Scottish

British White

British White

White Americans

White Americans

American Indian

You can just create a calculate table use follwing code

Table 2 = VALUES('Table'[Type])

Create a calculate column

Category = 
SWITCH(
    TRUE(),
    'Table 2'[Type] IN {"White British","White British Welsh","White Scottish","British White"}, "White British",
    'Table 2'[Type] IN {"White Americans","American Indian"},"American"
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[DataNinja777]

Hi @RFJammer ,

To create a new table with distinct values and add a manual column for grouping purposes in Power BI, you can use Power Query, Transform Data window. Here’s how you can do it:

First, open the Power Query Editor by going to the Home tab in Power BI Desktop and selecting "Transform Data." Once you're in the editor, locate your student table in the list of queries on the left and select the column containing the inconsistent ethnicity values. To extract a list of unique ethnicity values, go to the Home tab and click "Remove Duplicates." This will give you a distinct list of the ethnicities.

Next, create a new query by right-clicking the table in the left pane and selecting "Reference." This action will generate a new query based on the distinct ethnicity list. Rename this new query to something like "Ethnicity Groups" to keep things organized.

After creating the new query, you can add a new column to group the ethnicities. To do this, go to the Add Column tab in Power Query and click on "Custom Column." Give the column a name, such as "Group," and set a default value like "Unassigned" or leave it blank. Once the column is created, you can manually type values directly into the cells of this column within the Power Query Editor to assign group names to the corresponding ethnicities. For example, you could group entries like "White British," "White British Welsh," and "British White" under the label "White British."

When you're satisfied with the groupings, click "Close & Apply" to load the new table back into Power BI. This table, now containing your ethnicities and their corresponding groups, can be used in your data model for lookups or relationships.

If the inconsistencies in the data are extensive and require significant manual effort, you might find it easier to export the distinct ethnicity list from Power BI to Excel, update the groupings there, and then re-import the updated list as a mapping table. This can streamline the process if you’re more comfortable working in Excel.

Let me know if you'd like any further guidance!

Best regards,

[RFJammer]

Thanks for the help. I'm gettng stuck at the following part where you say "Once the column is created, you can manually type values directly into the cells of this column within the Power Query Editor to assign group names to the corresponding ethnicities. "

So I get to the point where I have generate a list of distinct ethnicities from my student table, and have added a custom column called 'group' with all values as 'unassigned'. But I cannot click on those values to overwrite them, they are locked as unassigned.