Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I am trying to Calculate Sprint Commitmment.There are some stories that are not completed in the same sprint and gets moved to next sprint.In such cases i dont want to add this story points twice. Here in this case if yiu refer Issue Key 68173 gets executed in multiple sprint.So in case if i sum it then it will keep adding the numbers of sprints it appears rather the need is to consider it once (unique).So here i need a power query that will calculate the sum of all story points committed considering the unique Issue Keys.
ISSUE_KEY | SPRINT_NAME | SPRINT_NUMBER | SPRINT_REPORT_STATUS | STORY_POINTS_COMMITTED |
54244 | Sprint 202 | 202 | COMPLETED | null |
62009 | Sprint 203 | 203 | INCOMPLETED | 0 |
62009 | Sprint 204 | 204 | COMPLETED | null |
65726 | Sprint 202 | 202 | INCOMPLETED | null |
65726 | Sprint 203 | 203 | INCOMPLETED | null |
65726 | Sprint 204 | 204 | REMOVED | null |
66113 | Sprint 202 | 202 | COMPLETED | 0 |
66198 | Sprint 203 | 203 | COMPLETED | 0 |
68164 | Sprint 204 | 204 | COMPLETED | 0 |
68173 | Sprint 203 | 203 | INCOMPLETED | 0 |
68173 | Sprint 204 | 204 | INCOMPLETED | 5 |
68173 | Sprint 205 | 205 | COMPLETED | 5 |
68651 | Sprint 202 | 202 | INCOMPLETED | 5 |
68651 | Sprint 203 | 203 | COMPLETED | 5 |
68653 | Sprint 205 | 205 | INCOMPLETED | 3 |
68799 | Sprint 202 | 202 | COMPLETED | 3 |
68888 | Sprint 202 | 202 | COMPLETED | 4 |
69669 | Sprint 202 | 202 | COMPLETED | 0 |
69825 | Sprint 202 | 202 | COMPLETED | 3 |
69827 | Sprint 202 | 202 | INCOMPLETED | 4 |
69827 | Sprint 203 | 203 | INCOMPLETED | 4 |
69827 | Sprint 204 | 204 | COMPLETED | 4 |
70191 | Sprint 205 | 205 | COMPLETED | 0 |
70244 | Sprint 203 | 203 | COMPLETED | 0 |
70249 | Sprint 203 | 203 | COMPLETED | 0 |
70448 | Sprint 204 | 204 | COMPLETED | 0 |
70588 | Sprint 204 | 204 | COMPLETED | 0 |
70694 | Sprint 202 | 202 | INCOMPLETED | 2 |
70694 | Sprint 203 | 203 | COMPLETED | 2 |
70695 | Sprint 202 | 202 | INCOMPLETED | 2 |
70695 | Sprint 203 | 203 | COMPLETED | 2 |
70710 | Sprint 202 | 202 | COMPLETED | 0 |
70720 | Sprint 202 | 202 | INCOMPLETED | 0 |
70720 | Sprint 203 | 203 | COMPLETED | null |
70787 | Sprint 202 | 202 | INCOMPLETED | 2 |
70787 | Sprint 203 | 203 | INCOMPLETED | 2 |
70787 | Sprint 204 | 204 | COMPLETED | 2 |
70789 | Sprint 203 | 203 | INCOMPLETED | 0 |
70789 | Sprint 204 | 204 | INCOMPLETED | 3 |
70789 | Sprint 205 | 205 | COMPLETED | 3 |
70868 | Sprint 202 | 202 | COMPLETED | 0 |
71109 | Sprint 202 | 202 | COMPLETED | 0 |
71286 | Sprint 202 | 202 | COMPLETED | 0 |
71325 | Sprint 202 | 202 | INCOMPLETED | 0 |
71325 | Sprint 203 | 203 | COMPLETED | null |
71358 | Sprint 204 | 204 | COMPLETED | 0 |
71439 | Sprint 203 | 203 | COMPLETED | 0 |
71499 | Sprint 203 | 203 | INCOMPLETED | 0 |
71499 | Sprint 204 | 204 | COMPLETED | null |
71532 | Sprint 203 | 203 | COMPLETED | 0 |
71775 | Sprint 204 | 204 | COMPLETED | 0 |
71880 | Sprint 203 | 203 | INCOMPLETED | 0 |
71880 | Sprint 204 | 204 | COMPLETED | null |
71940 | Sprint 203 | 203 | INCOMPLETED | 0 |
71940 | Sprint 204 | 204 | COMPLETED | null |
71942 | Sprint 203 | 203 | INCOMPLETED | 0 |
71942 | Sprint 204 | 204 | COMPLETED | null |
72035 | Sprint 204 | 204 | REMOVED | 0 |
72103 | Sprint 204 | 204 | COMPLETED | 0 |
72218 | Sprint 204 | 204 | COMPLETED | 0 |
72480 | Sprint 204 | 204 | COMPLETED | 0 |
72518 | Sprint 204 | 204 | COMPLETED | 0 |
72547 | Sprint 204 | 204 | COMPLETED | 0 |
72556 | Sprint 204 | 204 | INCOMPLETED | 0 |
72556 | Sprint 205 | 205 | COMPLETED | null |
72559 | Sprint 204 | 204 | INCOMPLETED | 0 |
72559 | Sprint 205 | 205 | COMPLETED | null |
72578 | Sprint 204 | 204 | INCOMPLETED | 0 |
72578 | Sprint 205 | 205 | INCOMPLETED | null |
72598 | Sprint 205 | 205 | INCOMPLETED | null |
72599 | Sprint 205 | 205 | COMPLETED | null |
72600 | Sprint 205 | 205 | INCOMPLETED | null |
72766 | Sprint 205 | 205 | COMPLETED | 0 |
72821 | Sprint 205 | 205 | INCOMPLETED | 0 |
72963 | Sprint 205 | 205 | INCOMPLETED | 0 |
Solved! Go to Solution.
Done.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZNTgMxDIWvgmbNInbsOFlDF0iUIkBsqh6ADUKI+4v0dzqpPeN0MepIn57ePDuOt9uBCYmG++H95/fr++8OA9aX4/Nhs359Xn2sHuv/MOzut0PCEMo1HA/w/vn04sDpgJOuzYJJN6Jqt/iClRYfrbyt1pvPazQBRHciCUrWbShwhkTuPDJI7PjAFh+1pzjrOB9wbqyc4cTgKY2B65mMsGFkqh1PuJSiW9Hg+luG6QiXlBzKp7RLRnbbqLB44iMdtwpv4HpTHWEJUEBP+/YjJTSjYa6397AxGjSYKC97PsOcO+BUjHE2DQ91XHc9wkbRLW32awsEb/dVGA1YGw03+HxpJLuaFXXcalYD12s5wh23zQ1uTcCo4/pROMM5OSbJyQhAcE8SAczGtafB0Ro7aiItPlt4iOw/ZkDRf9qBSk8dW3zeCEf0GxFhv3LOxqnRXTf4vHahLu0GX9I2ErG00a1ddYwA2y1KEIKxj2i6CP7eQ+pIGrlHmckxnS4wGxulmnOLz968FXaNMgNf0BYjEUN7ilv72QUvnXiH8xRCj7akjsQzGouRrl2Sa2ut+O4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISSUE_KEY = _t, SPRINT_NAME = _t, SPRINT_NUMBER = _t, SPRINT_REPORT_STATUS = _t, STORY_POINTS_COMMITTED = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"STORY_POINTS_COMMITTED", Int64.Type}, {"ISSUE_KEY", Int64.Type}, {"SPRINT_NUMBER", Int64.Type}}),
GroupedRows = Table.Group(ChangedType, {"ISSUE_KEY"}, {
{"All", each _, type table [SSUE_KEY=nullable number, SPRINT_NAME=nullable text, SPRINT_NUMBER=nullable number, SPRINT_REPORT_STATUS=nullable text, STORY_POINTS_COMMITTED=nullable number]},
{"Points", each List.Max([STORY_POINTS_COMMITTED]), Int64.Type} }),
BufferedPoints = List.Buffer(GroupedRows[Points]),
GenerateHelper = List.Generate(
()=> [ x = 0, y = BufferedPoints{x} ],
each [x] < List.Count(BufferedPoints),
each [ x = [x]+1, y = [y] + BufferedPoints{x} ],
each [y]
),
Merged = Table.FromColumns(Table.ToColumns(GroupedRows) & { {0} & List.RemoveLastN(GenerateHelper, 1)}, Value.Type(GroupedRows & #table(type table[Helper=Int64.Type],{}))),
Ad_InnerColumn = Table.AddColumn(Merged, "All With Total Column", each Table.AddColumn([All], "Total Story Points Committed", (x)=> x[STORY_POINTS_COMMITTED] + [Helper], Int64.Type), type table),
Combined = Table.Combine(Ad_InnerColumn[All With Total Column])
in
Combined
Thank you
Yes the requirement here is the sum all the individual story points keeping in mind to consider only Unique issue keys (Issues Key only once) if they appear in multiple sprints.Consider the ones with highest story points for summation.
So as in case of 68173 we should consider 5 (Highest story point) only once for summation.I would like to replace all null with zero for STORY_POINTS_COMMITTED column and add a new column called Total_Committed_Story_Points and sum it considering the issue key with Max story points once.Something like below
ISSUE_KEY | SPRINT_NAME | SPRINT_NUMBER | SPRINT_REPORT_STATUS | STORY_POINTS_COMMITTED | Total Story Points committed |
54244 | Sprint 202 | 202 | COMPLETED | 0 | 0 |
62009 | Sprint 203 | 203 | INCOMPLETED | 0 | 0 |
62009 | Sprint 204 | 204 | COMPLETED | 0 | 0 |
65726 | Sprint 202 | 202 | INCOMPLETED | 0 | 0 |
65726 | Sprint 203 | 203 | INCOMPLETED | 0 | 0 |
65726 | Sprint 204 | 204 | REMOVED | 0 | 0 |
66113 | Sprint 202 | 202 | COMPLETED | 0 | 0 |
66198 | Sprint 203 | 203 | COMPLETED | 0 | 0 |
68164 | Sprint 204 | 204 | COMPLETED | 0 | 0 |
68173 | Sprint 203 | 203 | INCOMPLETED | 0 | 0 |
68173 | Sprint 204 | 204 | INCOMPLETED | 5 | 5 |
68173 | Sprint 205 | 205 | COMPLETED | 5 | 5 |
68651 | Sprint 202 | 202 | INCOMPLETED | 5 | 10 |
68651 | Sprint 203 | 203 | COMPLETED | 5 | 10 |
68653 | Sprint 205 | 205 | INCOMPLETED | 3 | 13 |
68799 | Sprint 202 | 202 | COMPLETED | 3 | 16 |
68888 | Sprint 202 | 202 | COMPLETED | 4 | 20 |
69669 | Sprint 202 | 202 | COMPLETED | 0 | 20 |
69825 | Sprint 202 | 202 | COMPLETED | 3 | 23 |
69827 | Sprint 202 | 202 | INCOMPLETED | 4 | 27 |
69827 | Sprint 203 | 203 | INCOMPLETED | 4 | 27 |
69827 | Sprint 204 | 204 | COMPLETED | 4 | 27 |
70191 | Sprint 205 | 205 | COMPLETED | 0 | 27 |
70244 | Sprint 203 | 203 | COMPLETED | 0 | 27 |
70249 | Sprint 203 | 203 | COMPLETED | 0 | 27 |
70448 | Sprint 204 | 204 | COMPLETED | 0 | 27 |
70588 | Sprint 204 | 204 | COMPLETED | 0 | 27 |
70694 | Sprint 202 | 202 | INCOMPLETED | 2 | 29 |
70694 | Sprint 203 | 203 | COMPLETED | 2 | 29 |
70695 | Sprint 202 | 202 | INCOMPLETED | 2 | 31 |
70695 | Sprint 203 | 203 | COMPLETED | 2 | 31 |
70710 | Sprint 202 | 202 | COMPLETED | 0 | 31 |
70720 | Sprint 202 | 202 | INCOMPLETED | 0 | 31 |
70720 | Sprint 203 | 203 | COMPLETED | 0 | 31 |
70787 | Sprint 202 | 202 | INCOMPLETED | 2 | 33 |
70787 | Sprint 203 | 203 | INCOMPLETED | 2 | 33 |
70787 | Sprint 204 | 204 | COMPLETED | 2 | 33 |
70789 | Sprint 203 | 203 | INCOMPLETED | 0 | 33 |
70789 | Sprint 204 | 204 | INCOMPLETED | 3 | 36 |
70789 | Sprint 205 | 205 | COMPLETED | 3 | 36 |
70868 | Sprint 202 | 202 | COMPLETED | 0 | 36 |
71109 | Sprint 202 | 202 | COMPLETED | 0 | 36 |
71286 | Sprint 202 | 202 | COMPLETED | 0 | 36 |
71325 | Sprint 202 | 202 | INCOMPLETED | 0 | 36 |
71325 | Sprint 203 | 203 | COMPLETED | 0 | 36 |
71358 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
71439 | Sprint 203 | 203 | COMPLETED | 0 | 36 |
71499 | Sprint 203 | 203 | INCOMPLETED | 0 | 36 |
71499 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
71532 | Sprint 203 | 203 | COMPLETED | 0 | 36 |
71775 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
71880 | Sprint 203 | 203 | INCOMPLETED | 0 | 36 |
71880 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
71940 | Sprint 203 | 203 | INCOMPLETED | 0 | 36 |
71940 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
71942 | Sprint 203 | 203 | INCOMPLETED | 0 | 36 |
71942 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
72035 | Sprint 204 | 204 | REMOVED | 0 | 36 |
72103 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
72218 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
72480 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
72518 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
72547 | Sprint 204 | 204 | COMPLETED | 0 | 36 |
72556 | Sprint 204 | 204 | INCOMPLETED | 0 | 36 |
72556 | Sprint 205 | 205 | COMPLETED | 0 | 36 |
72559 | Sprint 204 | 204 | INCOMPLETED | 0 | 36 |
72559 | Sprint 205 | 205 | COMPLETED | 0 | 36 |
72578 | Sprint 204 | 204 | INCOMPLETED | 0 | 36 |
72578 | Sprint 205 | 205 | INCOMPLETED | 0 | 36 |
72598 | Sprint 205 | 205 | INCOMPLETED | 0 | 36 |
72599 | Sprint 205 | 205 | COMPLETED | 0 | 36 |
72600 | Sprint 205 | 205 | INCOMPLETED | 0 | 36 |
72766 | Sprint 205 | 205 | COMPLETED | 0 | 36 |
72821 | Sprint 205 | 205 | INCOMPLETED | 0 | 36 |
72963 | Sprint 205 | 205 | INCOMPLETED | 0 | 36 |
Done.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZNTgMxDIWvgmbNInbsOFlDF0iUIkBsqh6ADUKI+4v0dzqpPeN0MepIn57ePDuOt9uBCYmG++H95/fr++8OA9aX4/Nhs359Xn2sHuv/MOzut0PCEMo1HA/w/vn04sDpgJOuzYJJN6Jqt/iClRYfrbyt1pvPazQBRHciCUrWbShwhkTuPDJI7PjAFh+1pzjrOB9wbqyc4cTgKY2B65mMsGFkqh1PuJSiW9Hg+luG6QiXlBzKp7RLRnbbqLB44iMdtwpv4HpTHWEJUEBP+/YjJTSjYa6397AxGjSYKC97PsOcO+BUjHE2DQ91XHc9wkbRLW32awsEb/dVGA1YGw03+HxpJLuaFXXcalYD12s5wh23zQ1uTcCo4/pROMM5OSbJyQhAcE8SAczGtafB0Ro7aiItPlt4iOw/ZkDRf9qBSk8dW3zeCEf0GxFhv3LOxqnRXTf4vHahLu0GX9I2ErG00a1ddYwA2y1KEIKxj2i6CP7eQ+pIGrlHmckxnS4wGxulmnOLz968FXaNMgNf0BYjEUN7ilv72QUvnXiH8xRCj7akjsQzGouRrl2Sa2ut+O4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISSUE_KEY = _t, SPRINT_NAME = _t, SPRINT_NUMBER = _t, SPRINT_REPORT_STATUS = _t, STORY_POINTS_COMMITTED = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"STORY_POINTS_COMMITTED", Int64.Type}, {"ISSUE_KEY", Int64.Type}, {"SPRINT_NUMBER", Int64.Type}}),
GroupedRows = Table.Group(ChangedType, {"ISSUE_KEY"}, {
{"All", each _, type table [SSUE_KEY=nullable number, SPRINT_NAME=nullable text, SPRINT_NUMBER=nullable number, SPRINT_REPORT_STATUS=nullable text, STORY_POINTS_COMMITTED=nullable number]},
{"Points", each List.Max([STORY_POINTS_COMMITTED]), Int64.Type} }),
BufferedPoints = List.Buffer(GroupedRows[Points]),
GenerateHelper = List.Generate(
()=> [ x = 0, y = BufferedPoints{x} ],
each [x] < List.Count(BufferedPoints),
each [ x = [x]+1, y = [y] + BufferedPoints{x} ],
each [y]
),
Merged = Table.FromColumns(Table.ToColumns(GroupedRows) & { {0} & List.RemoveLastN(GenerateHelper, 1)}, Value.Type(GroupedRows & #table(type table[Helper=Int64.Type],{}))),
Ad_InnerColumn = Table.AddColumn(Merged, "All With Total Column", each Table.AddColumn([All], "Total Story Points Committed", (x)=> x[STORY_POINTS_COMMITTED] + [Helper], Int64.Type), type table),
Combined = Table.Combine(Ad_InnerColumn[All With Total Column])
in
Combined