Power Query - Create column - Ranking within multiple columns

Hi jerryr125 

I would suggest using Table.Group to create nested tables grouped by DivisionID and ProductID with Ranking columns added, then expanding the nested tables.

 

Something like this:

let
  Source = #table(
    type table [StoreID = text, DivisionID = Int64.Type, ProductID = Int64.Type, Quality = Int64.Type, Cost = Int64.Type],
    {
      {"A", 1, 100, 5, 45},
      {"B", 1, 100, 15, 55},
      {"C", 1, 200, 25, 65},
      {"D", 1, 200, 44, 25},
      {"E", 1, 200, 36, 15},
      {"F", 2, 100, 38, 12},
      {"G", 2, 100, 32, 5},
      {"H", 2, 100, 65, 8},
      {"I", 2, 100, 77, 2}
    }
  ),
  TableTypeWithRanking = Value.Type(Table.AddColumn(Source, "Ranking", each null, Int64.Type)),
  // Add Ranking column to nested table
  #"Nested TableWithRanking" = Table.Group(
    Source,
    {"DivisionID", "ProductID"},
    {{"TableWithRanking", each Table.AddRankColumn(_, "Ranking", {"Cost", Order.Ascending}), TableTypeWithRanking}}
  ),
  #"Final TableWithRanking" = Table.Combine(#"Nested TableWithRanking"[TableWithRanking])
in
  #"Final TableWithRanking"

 

Here's a video showing a similar method:

https://www.youtube.com/watch?v=ysDBHMbtXsk

 

Does this work for you?

Hi jerryr125 ,

Thank you for reaching out to the Microsoft Community Forum.

 

As per your inputs, you want to create column, to rank using multiple columns in power query.

 

Please follow below steps.

 

1. Created M code with below code in advanced editor.

 

let
Source = Table.FromRows({
{"A", 1, 100, 5, 45},
{"B", 1, 100, 15, 55},
{"C", 1, 200, 25, 65},
{"D", 1, 200, 44, 25},
{"E", 1, 200, 36, 15},
{"F", 2, 100, 38, 12},
{"G", 2, 100, 32, 5},
{"H", 2, 100, 65, 8},
{"I", 2, 100, 77, 2}
}, {"StoreID", "DivisionID", "ProductID", "Qualtity", "Cost"}),

ChangedTypes = Table.TransformColumnTypes(Source,{
{"DivisionID", Int64.Type},
{"ProductID", Int64.Type},
{"Qualtity", Int64.Type},
{"Cost", Int64.Type}
}),

Sorted = Table.Sort(ChangedTypes, {
{"DivisionID", Order.Ascending},
{"ProductID", Order.Ascending},
{"Cost", Order.Ascending}
}),

Grouped = Table.Group(Sorted, {"DivisionID", "ProductID"}, {
{"AllData", each Table.AddIndexColumn(_, "Ranking", 1, 1, Int64.Type)}
}),

Expanded = Table.ExpandTableColumn(Grouped, "AllData", {
"StoreID", "Qualtity", "Cost", "Ranking"
}),

Final = Table.SelectColumns(Expanded, {
"StoreID", "DivisionID", "ProductID", "Qualtity", "Cost", "Ranking"
})
in
Final

 

2.  Please refer below output snap and attached PBIX file.

 

 

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you