Forum Discussion

RobRoos's avatar
RobRoos
Frequent Visitor
4 years ago
Solved

Select rows on minimum value in Power Query

Hi, I have this table and I want to select the rows per LDMserver by the lowest value in LDMranking: Not all ranking starts at 1. So for LSRV1134 I want to keep the row with ranking = 1 for LSRV1...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi RobRoos 

     

    You can use User Interface in Power Query Editor to get the same result. Steps are:

     

    1. Select Transform > Group By, group on LDMserver column and perform two actions: 

    Min Ranking: Min on LDMranking

    All Data: All Rows

     

    2. Expand All Data column

     

    3. Select Add Column > Conditional Column. If LDMranking equals to Min Ranking, it returns 1. Otherwise it returns 0. 

     

    4. Filter IsMinRanking? column to keep rows whose value is 1. 

     

    5. Remove IsMinRanking? and Min Ranking columns. 

     

    Result

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

You could actually do this just by sorting ascending on LDMServer, then also Ranking, then remove duplicates.  But the proper way is to group on LDMServer, and use the Min aggregation on LDMRanking to grab your minimum ranking per group.

 

--Nate