cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NA Regular Visitor
Regular Visitor

Return Column if Highest Value

Hello, 

 

I have a data set with individuals education info. (See attached data sample, snippet). The data consists of info on 2200 people. 

 

I'm wanting to clean the data, so that it only shows me the highest Degree weight (0-5) associated with the ID. As you can see my data is repeating multiple times, I want it to clean up and show only one row of data, with the ID being the unique identifer. 

 

Any thoughts or Suggestions? 

 

Thanks, 

 

 

Sample Education Data.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Return Column if Highest Value

If you're OK throwing away all the data in the rows with duplicated IDs, then this is very easy in Power Query.

 

Open the query editor, and sort the table by Degree Weight.  Then right click the ID column header and select Remove Duplicates.  This will keep only the data rows where the ID first appears, which right now is ordered by Degree Weight.

 

 

If this is throwing away too much data, (like you want each person's highest weighted degree in each field of study) you can CTRL+click each of the columns that you want to keep unique values in, right click, and remove duplicates.

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Return Column if Highest Value

If you're OK throwing away all the data in the rows with duplicated IDs, then this is very easy in Power Query.

 

Open the query editor, and sort the table by Degree Weight.  Then right click the ID column header and select Remove Duplicates.  This will keep only the data rows where the ID first appears, which right now is ordered by Degree Weight.

 

 

If this is throwing away too much data, (like you want each person's highest weighted degree in each field of study) you can CTRL+click each of the columns that you want to keep unique values in, right click, and remove duplicates.

View solution in original post

tarunsingla Established Member
Established Member

Re: Return Column if Highest Value

Not sure if meant to makes changes to the data model or just want a visual with just the max value per id.

Check this if you need the latter.

https://docs.microsoft.com/en-us/power-bi/service-aggregates

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 276 members 2,895 guests
Please welcome our newest community members: