Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
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.
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"
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