Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.