Saturday, December 10, 2022

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


#--------------------------------------------------------------Thanks--------------------------------------------------------------#

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog