Power Query - Create column - Ranking within multiple columns

[jerryr125]

Hi - 

I have the following table and I will like to rank using multiple columns.

table-abc

StoreID	DivisionID	ProductID	Qualtity	Cost
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

Output:

Ranking by DivisionID, ProductID and Lowest Cost

Any Assistance is appreciated!  Jerry

StoreID	DivisionID	ProductID	Qualtity	Cost	Ranking
A	1	100	5	45	1
B	1	100	15	55	2
E	1	200	36	15	1
D	1	200	44	25	2
C	1	200	25	65	3
I	2	100	77	2	1
G	2	100	32	5	2
H	2	100	65	8	3
F	2	100	38	12	4

[jerryr125 · Accepted solution]

This was a very helpful video

https://youtu.be/ej60Wxaum_E?si=DD2VYtOKQThHa2ce

[v-dineshya]

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

[OwenAuger]

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?