Create a "Rank" field for a grouping within a grouping

[knuckledown234]

Edit: I actually found my answer and described it in the reply.

Goal: I have a table that shows retail store transactions. I need to add a "Rank" field to this table, which shows the store's total yearly sales rank among other stores' yearly sales within the same region.

Additional notes:
- Unfortunately I'm not querying a SQL database, or else I would write a RANK() window function that ranks each store by it's total sales partitioned by Region and Year.

- I tried writing a DAX RANK window function as a calculated column that works in Power BI Desktop and in the PBI Service. The function works in the Power BI Desktop and in the PBI Service, but oddly it gives the infamous "The query referenced calculated column which does not hold any data because there is an error in its expression" error in the Power BI service after a refresh. So, I am trying to create this ranking in power query. 

- I am following Rick de Groot's awesome video "Create Index Column by Group in Power Query." (Create Index Column By Group in Power Query (youtube.com)) In the video, Rick is able to sort rows by color name, group rows by Color Group, and add an index column that maintains the sorted row order. I'm realizing he only groups once (by color group), and I group twice, so I am not able to maintain the sorted row order by following his instructions. I'm also wondering if I'm doing something wrong. 

Here is my example code:

Source = (my source)

#"Sorted Rows" = Table.Sort(Source,{{"Year", Order.Ascending}, {"Region", Order.Ascending}, {"Store", Order.Ascending}, {"Sales", Order.Descending}}),

// I create a column that obtains each store's sales for the year.
#"Group by Year, Region" =
Table.Group(
#"Sorted Rows"
, {"Year", "Region", "Store"}
, {
{"Gr", each _, type table}, // This "Gr" item  stores my other columns, which I want to keep after I create my rank field.
{"TOTAL_SALES_PER_STORE_GROUPED_BY_YEAR_REGION", each List.Sum([Sales]), type nullable number} // This field stores sales by store for the year.
}
),

 // This sort step is where I sort the table by Year, Region, and TOTAL_SALES, since I want a ranking of each store's yearly total sales within it's region, within that year of sales.

#"Sort by Year, Region, TOTAL_SALES" =
Table.Sort(
#"Group by Year, Region"
,{
{"Year", Order.Ascending}, {"Region", Order.Ascending}, {"TOTAL_SALES", Order.Descending}
}
),

#"Grouped Rows by Region, Year" =
Table.Group(
#"Sort by Year, Region, TOTAL_SALES"
, {"Year", "Region"}
, {
{"Details"
, each Table.AddIndexColumn(
_
, "Store_Rank_Year_Region_Partition"
, 1
,1
, Int64.Type
), type table
}
}
),

// When I click on a cell in the "Details" field at this step, it shows that the funded amounts are no longer in the same sort order as the #"Sort by Year, Region, TOTAL_SALES" step.

#"Expand Table" =
Table.ExpandTableColumn(
#"Grouped Rows by Region, Year"
, "Details"
, {"Store", "Gr", "TOTAL_FUNDING", "Store_Rank_Year_Region_Partition"}
, {"Store", "Gr", "TOTAL_FUNDING", "Store_Rank_Year_Region_Partition"}
),

 Is there something I am missing? Is there a way I can sort my nested table that I create in #"Grouped Rows by Region, Year" step?

I'm starting to think I might need to merge the table with itself and create an index that starts over whenever the value in Region or Year changes, but I'm wondering if there is a more efficient way.

[knuckledown234 · Accepted solution]

I continued searching after I made this post, and I think I found the answer. I should have used a function called Table.AddRankColumn instead of attempting to sort and then add an index.

Radacad has a great article and video on Table.AddRankColumn here: Adding Pre-Calculated Rank in Power BI Using Power Query - RADACAD

To see how I applied it, this is my old code rewritten with Table.AddRankColumn:

#"Grouped Rows by Region, Year" =
Table.Group(
#"Sort by Year, Region, TOTAL_SALES"
, {"Year", "Region"}
, {
{"Details"
, each Table.AddIndexColumn(
_
, "Store_Rank_Year_Region_Partition"
, 1
,1
, Int64.Type
), type table
}
}
),

This is my new code:

Source = (my source)

// I create group the [Sales] column by year, region and store, which gives me a column that sums each store's sales for the year.
#"Group by Year, Region, store to obtain total sales" =
Table.Group(
Source

, {"Year", "Region", "Store"}
, {
{"Gr", each _, type table}, // This "Gr" item  stores my other columns, which I want to keep after I create my rank field.
{"TOTAL_SALES_PER_STORE_GROUPED_BY_YEAR_REGION", each List.Sum([Sales]), type nullable number} // This field stores sales by store for the year.
}
),

// This is the step where I create a nested table with basic store details: rank, store, store total sales and year. This will be used to create the ranking.

    #"Grouped Rows by Region, Year"  =

        Table.Group(

#"Group by Year, Region, store to obtain total sales"

            , {"Year", "Region"}

                , {

                    {"Details"

                        , each _

                        , type table [Year=Int64.Type, Region=Text.Type, Store=Int64.Type, TOTAL_SALES_PER_STORE_GROUPED_BY_YEAR_REGION =Number.Type]

                    }

                }

        )

// This is the actual Store sales ranking column.

    ,#"Add Store Sales Rank within Year, Region Partitions" =

    Table.AddColumn(

        #"Grouped Rows by Region, Year" 

        , "Nested table with store ranking"

        , each

            Table.AddRankColumn(

                [Details] // This is the table to be ranked; we are providing our column with nested tables

                , "Store_rank_by_region_year_Partition", {"TOTAL_SALES_PER_STORE_GROUPED_BY_YEAR_REGION ", Order.Descending} // This will be the name of our ranking column and the way it is to be ranked

            )

    )

[Ahmedx]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.