How to use Power Query to create Index Number for each Partition Group in Power BI

How to use Power Query to get the Previous Row values based on Row Number or Index Number of the Partition Group in Power BI
Scenario:

We have a Dataset with Fields : [Order Date], [Prod ID], [Prod Name], [Product Category] and [Order Quantity]

In this Dataset, we need add the Row or Index Number for each Partition by [Product Category] and Order by based on the Sum of [Order Qnty].

We can implement this by following the below Steps.

1) Group the Data by {"Prod ID", "Prod Name", "Prod Category"} and Sort by [Order Qnty]:

First we need to Group the Data, to calculate the [Total Qnty] for each [Prod Id], which gives the one Row for each [Prod ID] and [Prod Category].

GroupData:

= Table.Group(#"Changed Type", {"Prod ID", "Prod Name", "Prod Category"}, {{"Total Qnty", each List.Sum([Order Qnty]), type nullable number}})

SortGroupData:

= Table.Sort(GroupData,{{"Prod Category", Order.Ascending}, {"Total Qnty", Order.Descending}})

Result:

2) Re Group the Data by {"Prod Category"} and Sort by [Order Qnty]:

We will  Re-Group the above dataset by [Prod Category], hence we can get a Sub set or the Partition tables for each [Prod Category]. Next will add the Index No for each Partition Tables.

ReGroupIndex:

= Table.Group(SortGroupData, {"Prod Category"}, 

        {{"Details", each Table.AddIndexColumn(_, "Index No", 0, 1, Int64.Type), type table}})

Result:

3) Get the [Total Qnty] of Previous row based on the Index Number:

We can get the [Total Qnty] value of the previous row based on the Index Number, as shown below.

GetPrevQnty:

= Table.AddColumn(ReGroupIndex, "Prev Qnty Details", each let 

            vCurTable = [Details], 

            vPrevRowVal = Table.AddColumn(vCurTable, "Prev Qnty",

            each try vCurTable[Total Qnty] {[Index No]-1}

            otherwise null

            )

        in  

        vPrevRowVal)

Result:

4) Combine and extract all the data from Partition tables with Index and Prev Qnty details:

DataWithIndexDetails:

= Table.Combine(GetPrevQnty[Prev Qnty Details])

Result:

Notes:

Here are the complete Power Query steps used in the above Example:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFRa4MwEAfw7+KzoLkk6h63sb2VllL6Ij5kXZCAS0rM9vl7sb1MN0Ynwkngl+Pyv7bNZFEXUEKZ5RkDjnWv3o3D/8ugT8E7a04jnmTW5RE3/8H8hllJGrjAetCD/jKjcfbXFbH+CtAVlmYSEutG2T7O9Oo/TYiQlSThjqwI8jRFHeGTsvjNJeXBRJJTy63Hnnqc05qo/KaA9fF8HvQcNgSrOzCFlbbBRYV154IK8UFH3eug3ga9eNQDaSGjflbeu/BTN6t6p2ghLUHAlIO9rmyhKTRIexBTvAf38Ufv7gI=", BinaryEncoding.Base64), Compression.Deflate)),

 let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, 

#"Prod ID" = _t, #"Prod Name" = _t, #"Prod Category" = _t, #"Order Qnty" = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Prod ID", Int64.Type}, {"Prod Name", type text}, {"Prod Category", type text}, {"Order Qnty", Int64.Type}}),

GroupData = Table.Group(#"Changed Type", {"Prod ID", "Prod Name", "Prod Category"}, {{"Total Qnty", each List.Sum([Order Qnty]), type nullable number}}),

SortGroupData = Table.Sort(GroupData,{{"Prod Category", Order.Ascending}, {"Total Qnty", Order.Descending}}),

ReGroupIndex = Table.Group(SortGroupData, {"Prod Category"},  {{"Details", each Table.AddIndexColumn(_, "Index No", 0, 1, Int64.Type), type table}}),

GetPrevQnty = Table.AddColumn(ReGroupIndex, "Prev Qnty Details", each let 

            vCurTable = [Details], 

            vPrevRowVal = Table.AddColumn(vCurTable, "Prev Qnty",

            each try vCurTable[Total Qnty] {[Index No]-1}

            otherwise null

            )

        in  

        vPrevRowVal),

    

DataWithIndexDetails = Table.Combine(GetPrevQnty[Prev Qnty Details])

in

    DataWithIndexDetails

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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