Group multiple columns and rank within the group power query

Question

I am trying to create a rank column by multiple fields and within individual groups. The purpose is to allow me to easily select the top 10 values for a set context. While this is theoretically possible in a DAX measure (for a dynamic context) in Power Pivot and Power Bi, it does not seem that it is possible to filter by a measure in a pivot table.

Below is the table transformation I want to do.

GroupField1	GroupField2	RankField1	RankField2	OtherField
Y	A	25	10	data
X	B	30	12	data
Y	A	20	10	data
X	B	15	10	data
Z	Q	5	5	data

Transforms to :

GroupField1	GroupField2	RankField1	RankField2	OtherField	Rank1	Rank2
Y	A	25	10	data	2	2
X	B	30	12	data	1	1
Y	A	20	10	data	3	2?
X	B	15	10	data	4	2?
Z	Q	5	5	data	5	3

Answer 1

I built a custom function to accomplish this

1. Group into nested tables

2. Process the nested tables to add the rankings

3. Expand the appropriate columns to return the table to its original form + the Rank columns

    RankGroup = (UnRanked as table, GroupFields as list, RankFields as list, RankNames as list) as table => let 
    //Group by GroupFields
    Grouped = Table.Group(UnRanked, GroupFields, {{"Ranked", each _}}),
   
    // Ranks Subfunction
    Ranks = (InVal as table, NewCols as list, Fields as list) as any => let
        // Soon to be replaceable Rank Function
        Rank = (InVal as table, NewCol as text, Field as text) as table => Table.AddIndexColumn(Table.Sort(InVal, {{Field, Order.Descending}}), NewCol),
        result = List.Accumulate(
            List.Positions(NewCols), InVal, (value, index) => Rank(value, NewCols{index}, Fields{index})
            )
        in 
            result,
    // Process each Group
    Ranked = Table.TransformColumns(Grouped, {"Ranked" ,each Ranks(_, RankFields, RankNames)}),
    // Only Expand the columns that are not represented in the Grouped table
    Expanded = Table.ExpandTableColumn(Ranked, "Ranked", List.RemoveItems(RankNames & Table.ColumnNames(UnRanked), GroupFields))
    in 
    Expanded
   

Note that the Rank function has a soon coming replacement (My Excel installation does not have this feature) Table.AddRankColumn

However, this can easily be accomplished using DAX in Power BI or Power Pivot: For an individual calculated rank within a particular group, we can use the RANKX function

Rank rows in a group RANKX ( 
FILTER( 'SourceTable',
    'SourceTable'[GroupByField1] = EARLIER('SourceTable'[GroupByField1]) &&
    'SourceTable'[GroupByField2] = EARLIER('SourceTable'[GroupByField2])
    ),
 'SourceTable'[RankField])

This DAX expression is adapted from Databear.com RANKX