If you are happy to transform the table, you can first group by ISSUE_KEY column and have all rows into a column. Like below, each table on a row has all rows related to that issue.
Then add a custom step to transform that "All Data" column and make it only remains the last sprint row. Finally expand the table column. As a result, only the last sprint record of each issue is left in the table.
Here is the full code you can paste into a blank query's Advanced Editor to check the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZhNS8NAEIb/ivSskP1M9mhrBEGt1KoH8VCwSDGk0lbBf28EwWQ2s7Oz3UMJhfLwfsxst31+npxPZ2dGOldOTif3H7tNezgRtureXN3O5jd31/WyvujeXZ5f39fdU3ev4vejh+3uu3vOmu1+/Tp5OUVIDiUZgqQqJSCpz1kuHuo/hAhhnHJhQT1Q+9k0PdZivd82XwF7ZQFERZtTzkDOor6ZPw4ogo6ogJTxsP+MBWHDoHgwPynoUKA4rkuclOBSjAYf5VDbqqRRihDUUao4d5ImedYSSc6qMImzM6aQXn//OYE1vmoP6127ak6mn28ReyOR/ePOlDxmpuAgyCyDgEviDsIRJDAIrJjGJkFECuP2p3L2h8O4HeIkbodqdKa4/Y1TkrrDrXG70zm7w2Hc7nASlTqMCidRURmrPZLfH7xszD/W7T/DShGZUNz3OmjPxI4U1ZzJ1twvKUdvGIdszTmdxxnsDidFdWdNZZJx/k1K97U5f678S8JytX/vHk+rzWHTvvWE6UGDbjjnPid8S7BSSQdxCfFbpYQNcdjCtC49n8cAvRLIbcQacEZXEHSUMi11bwlkEXOHZCKxy1/PLkUEZ5BNGxRv4222jbexx+vItuuiihNFHYzduZHLHjiBou1RiZfpkkBMOImMSRcujkR9g8DA092VykRqotyVRps8mmB3+L9N3O4qZH+5zeGKaJIowqTesScZI4BZ425cUBD4LU8NU2rcpR7+LAlqUoyZ5E/Syw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISSUE_KEY = _t, SPRINT_NAME = _t, SPRINT_REPORT_STATUS = _t, IS_ADDED_DURING_SPRINT = _t, STORY_POINTS_COMMITTED = _t, STORY_POINTS_EXTRA_PULLED = _t, ISSUE_TYPE_NAME = _t, ISSUE_STATUS_NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ISSUE_KEY", type text}, {"SPRINT_NAME", type text}, {"SPRINT_REPORT_STATUS", type text}, {"IS_ADDED_DURING_SPRINT", type logical}, {"STORY_POINTS_COMMITTED", Int64.Type}, {"STORY_POINTS_EXTRA_PULLED", Int64.Type}, {"ISSUE_TYPE_NAME", type text}, {"ISSUE_STATUS_NAME", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ISSUE_KEY"}, {{"All Data", each _, type table [ISSUE_KEY=nullable text, SPRINT_NAME=nullable text, SPRINT_REPORT_STATUS=nullable text, IS_ADDED_DURING_SPRINT=nullable logical, STORY_POINTS_COMMITTED=nullable number, STORY_POINTS_EXTRA_PULLED=nullable number, ISSUE_TYPE_NAME=nullable text, ISSUE_STATUS_NAME=nullable text]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows",{"All Data", each Table.FirstN(Table.Sort(_, {"SPRINT_NAME", Order.Descending}),1)}),
#"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"SPRINT_NAME", "SPRINT_REPORT_STATUS", "IS_ADDED_DURING_SPRINT", "STORY_POINTS_COMMITTED", "STORY_POINTS_EXTRA_PULLED", "ISSUE_TYPE_NAME", "ISSUE_STATUS_NAME"}, {"SPRINT_NAME", "SPRINT_REPORT_STATUS", "IS_ADDED_DURING_SPRINT", "STORY_POINTS_COMMITTED", "STORY_POINTS_EXTRA_PULLED", "ISSUE_TYPE_NAME", "ISSUE_STATUS_NAME"})
in
#"Expanded All Data"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
I have one question: for an issue that might appear multiple times in different sprints, the score of which sprint is you want to sum up? For example, Issue ABC-52997 appears three times (sprint 168, 169, 170) in your sample data, and the story points in different sprints are different (4 and 5). Which sprint do you hope to include in the TOTAL_STORY_POINTS_COMITTED? The first one or the last one? Or the one with a specified status? Please provide some expected outcome based on your sample data. Thanks!
Best Regards,
Jing
If you are happy to transform the table, you can first group by ISSUE_KEY column and have all rows into a column. Like below, each table on a row has all rows related to that issue.
Then add a custom step to transform that "All Data" column and make it only remains the last sprint row. Finally expand the table column. As a result, only the last sprint record of each issue is left in the table.
Here is the full code you can paste into a blank query's Advanced Editor to check the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZhNS8NAEIb/ivSskP1M9mhrBEGt1KoH8VCwSDGk0lbBf28EwWQ2s7Oz3UMJhfLwfsxst31+npxPZ2dGOldOTif3H7tNezgRtureXN3O5jd31/WyvujeXZ5f39fdU3ev4vejh+3uu3vOmu1+/Tp5OUVIDiUZgqQqJSCpz1kuHuo/hAhhnHJhQT1Q+9k0PdZivd82XwF7ZQFERZtTzkDOor6ZPw4ogo6ogJTxsP+MBWHDoHgwPynoUKA4rkuclOBSjAYf5VDbqqRRihDUUao4d5ImedYSSc6qMImzM6aQXn//OYE1vmoP6127ak6mn28ReyOR/ePOlDxmpuAgyCyDgEviDsIRJDAIrJjGJkFECuP2p3L2h8O4HeIkbodqdKa4/Y1TkrrDrXG70zm7w2Hc7nASlTqMCidRURmrPZLfH7xszD/W7T/DShGZUNz3OmjPxI4U1ZzJ1twvKUdvGIdszTmdxxnsDidFdWdNZZJx/k1K97U5f678S8JytX/vHk+rzWHTvvWE6UGDbjjnPid8S7BSSQdxCfFbpYQNcdjCtC49n8cAvRLIbcQacEZXEHSUMi11bwlkEXOHZCKxy1/PLkUEZ5BNGxRv4222jbexx+vItuuiihNFHYzduZHLHjiBou1RiZfpkkBMOImMSRcujkR9g8DA092VykRqotyVRps8mmB3+L9N3O4qZH+5zeGKaJIowqTesScZI4BZ425cUBD4LU8NU2rcpR7+LAlqUoyZ5E/Syw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISSUE_KEY = _t, SPRINT_NAME = _t, SPRINT_REPORT_STATUS = _t, IS_ADDED_DURING_SPRINT = _t, STORY_POINTS_COMMITTED = _t, STORY_POINTS_EXTRA_PULLED = _t, ISSUE_TYPE_NAME = _t, ISSUE_STATUS_NAME = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ISSUE_KEY", type text}, {"SPRINT_NAME", type text}, {"SPRINT_REPORT_STATUS", type text}, {"IS_ADDED_DURING_SPRINT", type logical}, {"STORY_POINTS_COMMITTED", Int64.Type}, {"STORY_POINTS_EXTRA_PULLED", Int64.Type}, {"ISSUE_TYPE_NAME", type text}, {"ISSUE_STATUS_NAME", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ISSUE_KEY"}, {{"All Data", each _, type table [ISSUE_KEY=nullable text, SPRINT_NAME=nullable text, SPRINT_REPORT_STATUS=nullable text, IS_ADDED_DURING_SPRINT=nullable logical, STORY_POINTS_COMMITTED=nullable number, STORY_POINTS_EXTRA_PULLED=nullable number, ISSUE_TYPE_NAME=nullable text, ISSUE_STATUS_NAME=nullable text]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows",{"All Data", each Table.FirstN(Table.Sort(_, {"SPRINT_NAME", Order.Descending}),1)}),
#"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"SPRINT_NAME", "SPRINT_REPORT_STATUS", "IS_ADDED_DURING_SPRINT", "STORY_POINTS_COMMITTED", "STORY_POINTS_EXTRA_PULLED", "ISSUE_TYPE_NAME", "ISSUE_STATUS_NAME"}, {"SPRINT_NAME", "SPRINT_REPORT_STATUS", "IS_ADDED_DURING_SPRINT", "STORY_POINTS_COMMITTED", "STORY_POINTS_EXTRA_PULLED", "ISSUE_TYPE_NAME", "ISSUE_STATUS_NAME"})
in
#"Expanded All Data"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Helpful resources
Power BI Dataviz World Championships
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
Power BI Monthly Update - December 2025
Check out the December 2025 Power BI Holiday Recap!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
