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

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,