How to Rank the Data for each Group in Power Query

How to apply Rank for each Group of Data in Power Query

Scenario:
Suppose we have the Sample dataset with columns as Product Segment, Product Name and Order Quantity as follows:

Now we want to apply the Ranking for the Order Quantity sort by Descending for each Product Segment sort by Ascending Order.

We can achieve this Scenario using the following Power Query:

let

    SourceData = "dY49C8IwEIb/SsncQaioq1alCF2k4FAynOlBD2Ou5EP8+cYMVaxd7ut5795rW3HQqLxlQ8qJXFRgu2xPTsW6EDL/5WfoiGNe/mMcPNoZ2KDGBzliE5t1EhyDNeSDxTjZYRfjajIve6DPyW/SwFXjuFPxHbPtMGgCo/BteAHX16B6Mjg6TlUl8y39vJkR1PRMvJjhp0AqCRZCyhc=",

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(SourceData, BinaryEncoding.Base64), Compression.Deflate)), 

        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProdSegment = _t, ProdName = _t, OrderQnty = _t]),

    ChangedType = Table.TransformColumnTypes(Source,{{"ProdSegment", type text}, {"ProdName", type text}, {"OrderQnty", Int64.Type}}),

    SegmentGroupData = Table.Group(ChangedType, {"ProdSegment"}, {{"ProdSegGroup", 

        each _, type table [ProdSegment=nullable text, ProdName=nullable text, OrderQnty=nullable number]}}),

    RankQnty = Table.AddColumn(SegmentGroupData, "RankByProdQnty", each Table.AddRankColumn([ProdSegGroup], 

        "RankProdQnty",{{"ProdSegment",Order.Ascending},{"OrderQnty",Order.Descending}},[RankKind = RankKind.Dense])),

    SelectColumns = Table.SelectColumns(RankQnty,{"RankByProdQnty"}),

    ExpandRankByProdQnty = Table.ExpandTableColumn(SelectColumns, "RankByProdQnty", 

        {"ProdSegment", "ProdName", "OrderQnty", "RankProdQnty"}, 

        {"Prod_Segment", "Prod_Name", "Order_Qnty", "Rank_ProdQnty"})

in

    ExpandRankByProdQnty 

Result:

Notes:

To apply Rank with Dense: [RankKind = RankKind.Dense]
To apply Rank without Dense: [RankKind = RankKind.Competition]

To apply Rank based on source data Order: [RankKind = RankKind.Ordinal]

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------