Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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, 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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.

2 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar
    7 years ago

    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.