Forum Discussion
Custom column Index or Ranking by other column
Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:
let Source = Table1, Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"}) in #"Expanded Partition"
This site may help. Even though it is power query the same techniques that use M code/DAX applies to Power BI. Look around the 20min mark in the video. It shows how to rank the same if the same.
23:03 / 30:42
MSPTDA 08: Power Query Group By feature & Table.Group Function
https://www.youtube.com/watch?v=hs21s0TWT14
I also recreated your data and added a few in case the second sort, by height also had a ‘match’
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Weight", Int64.Type}, {"Height", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Weight", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "IndexWeight", 1, 1),
#"Sorted Rows1" = Table.Sort(#"Added Index",{{"Height", Order.Descending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows1", "IndexHeight", 1, 1),
#"Grouped Rows" = Table.Group(#"Added Index1", {"Weight"}, {{"All Rows", each _, type table [Person=text, Weight=number, Height=number, IndexWeight=number, IndexHeight=number]}, {"Rank Weight", each List.Min([IndexWeight]), type number}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Person", "Weight", "Height", "IndexHeight"}, {"All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight"}),
#"Grouped Rows1" = Table.Group(#"Expanded All Rows", {"All Rows.Height"}, {{"Height", each _, type table [Weight=number, All Rows.Person=text, All Rows.Weight=number, All Rows.Height=number, All Rows.IndexHeight=number, Rank Weight=number]}, {"Rank Height", each List.Min([All Rows.IndexHeight]), type number}}),
#"Expanded Height" = Table.ExpandTableColumn(#"Grouped Rows1", "Height", {"Weight", "All Rows.Person", "All Rows.Weight", "All Rows.Height", "All Rows.IndexHeight", "Rank Weight"}, {"Height.Weight", "Height.All Rows.Person", "Height.All Rows.Weight", "Height.All Rows.Height", "Height.All Rows.IndexHeight", "Height.Rank Weight"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Height",{"Height.All Rows.Weight", "Height.All Rows.IndexHeight", "Height.All Rows.Height"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Height.Rank Weight", "Rank Weight"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Height.All Rows.Person", "All Rows.Height", "Height.Weight", "Rank Weight", "Rank Height"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"All Rows.Height", "Height"}, {"Height.Weight", "Weight"}}),
#"Sorted Rows2" = Table.Sort(#"Renamed Columns1",{{"Rank Weight", Order.Ascending}, {"Rank Height",Order.Ascending}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Sorted Rows2",{"Height.All Rows.Person", "Weight", "Height", "Rank Weight", "Rank Height"})
in
#"Reordered Columns1"
Result as below
I hope this helps
Someone might be able to make the code a bit tidier
Hi Anonymous
Thank you for your answer, but I think I am not clear about my question. I want to evaluate both criterion and create one ranking.
In the example, it should be like that:
Does it make sense now? ImkeF provided us with a solution for it in the blog, my issue is that the ranking is not proper for some observations, so my result may look like that:
Thank you, I appreciate any ideas to fix it.
Best, Qianru
