Select rows on minimum value in Power Query

[RobRoos]

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?

[v-jingzhang · Accepted solution]

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.

[ronrsnfld]

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"

[Anonymous]

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