How do I get a unique list ?

[EaglesTony]

hi,

 I have the following data:

 IDENTITY RoleName    RolePersonName

111            Cook             John Doe

111            Waitress        Jane Doe

111            Cook             Karen Mayberry

111            Asst Mgr       John Doe

222            Cook             John Doe

222            Cook             Frank Adams

222            Officer          Maryann James

I want the final output to be possibly 2 outputs:

IDENTITY RoleName    

111            Cook           

111            Waitress      

111            Asst Mgr

222            Cook            

222            Officer         

And

IDENTITY  RolePersonName

111            John Doe (only once)

111            Jane Doe

111            Karen Mayberry

222            John Doe

222            Frank Adams

222            Maryann James

[BA_Pete · Accepted solution]

Hi @EaglesTony ,

In Power Query you would use the Table.Distinct() function.

Your two new tables (if you want them to be new tables) would be created as follows:

-1- Reference your main table into two new queries

-2- Remove the column that won't be used in each.

-3- Press Ctrl+A to select the whole table (or Ctrl+Click both columns you want to have unique combinations of).

-4- Go to the Home tab > Remove Rows > Remove Duplicates.

This should create a new code line something like this:

= Table.Distinct(Source, {"IDENTITY", "RoleName"})

Pete

[v-ssriganesh]

Hello @EaglesTony,
Thank you for reaching out to the Microsoft fabric community forum.

I have reproduced your scenario in Power BI using the sample data you provided and was able to get the exact expected results two unique lists, one for RoleName and one for RolePersonName.

To achieve this, I used calculated tables in DAX instead of Power Query.
Below are the DAX expressions used:

Unique Roles Table:

UniqueRoles =
DISTINCT (

    SELECTCOLUMNS (

        Roles,

        "IDENTITY", Roles[IDENTITY],

        "RoleName", Roles[RoleName]

    )
)

Unique Persons Table :

UniquePersons =
DISTINCT (

    SELECTCOLUMNS (

        Roles,

        "IDENTITY", Roles[IDENTITY],

        "RolePersonName", Roles[RolePersonName]

    )
)

I’ve also attached a .pbix file for your reference showing the exact setup and results.

Best regards,
Ganesh Singamshetty.

[grazitti_sapna · Accepted solution]

Hi @EaglesTony ,

Please follow these steps to get the result:

1. In Home Tab, go to transform data, It will open power query editor for you.(Home → Transform Data → Power Query Editor.)
2. Right Click your main table and duplicate it and rename it 'Distinct Rolnames'
3.Select two columns that are needed holding CTRL and select Identity and Rolename and right click and select remove other columns.
4.Again select both the columns holding CTRL and select Identity and Rolename and right click and select remove duplicates or (Home → Remove Rows → Remove Duplicates.)
Now you can see the desired output.

5. Click Close and Apply.
6.Similar steps please follow for the 2nd output.
7. If you see summarization sign in front of identity  in Fields section then change it to "Do Not Summarize". It will wont add up the identity column and will give you desired output.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[BA_Pete]

Hi @grazitti_sapna ,

Can you give me a bit of detail around why your solution works better than the one I provided 10 hours prior please?

Is it faster/more efficient/using different functions that are more user-intuitive?

The only key difference I can see is that you suggest to Duplicate rather than Reference the original query which, in my opinion, is a backwards step in terms of future manageability of the the overall query structure.

Thanks,

Pete

[BA_Pete · Accepted solution]

Hi @EaglesTony ,

In Power Query you would use the Table.Distinct() function.

Your two new tables (if you want them to be new tables) would be created as follows:

-1- Reference your main table into two new queries

-2- Remove the column that won't be used in each.

-3- Press Ctrl+A to select the whole table (or Ctrl+Click both columns you want to have unique combinations of).

-4- Go to the Home tab > Remove Rows > Remove Duplicates.

This should create a new code line something like this:

= Table.Distinct(Source, {"IDENTITY", "RoleName"})

Pete