How to Create Row Number or Index Number for each Partition Group in Power BI using Power Query

How to get the Previous Row values based on Row Number or Index Number of the Partition Group in Power BI using Power Query
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

)

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 is 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