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:
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:
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:
DataWithIndexDetails:
= Table.Combine(GetPrevQnty[Prev Qnty Details])
Result:
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.