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 LSRV1135 the row with ranking = 2

etc.

 

How do I do this?

 

 

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.

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    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

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    Try this:

     

    let

//change table name in next line to actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LDMserver", type text}, {"LDMhighApp", type text}, {"LDMranking", Int64.Type}}),
   
    #"Grouped Rows" = Table.Group(#"Changed Type", {"LDMserver"}, {
                        {"lowest rank value", (t)=>
                            Table.SelectRows(t, each [LDMranking]=List.Min(t[LDMranking])),                      
                                type table[LDMserver=text, LDMhighApp=text, LDMranking=Int64.Type]}
                }),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"LDMserver"}),
    #"Expanded lowest rank value" = Table.ExpandTableColumn(#"Removed Columns", "lowest rank value", {"LDMserver", "LDMhighApp", "LDMranking"}, {"LDMserver", "LDMhighApp", "LDMranking"})
in
    #"Expanded lowest rank value"