Forum Discussion
Summarise Columns should not create multiple rows per unique ID
Hello, I need an adjustment to my summarise columns but I cannot figure out how to make the necessary adjustment.
Situation:
I have one table with the day-by-day status of PBIs. I use summarisecolumns to create a new table with just 1 row per PBI so that I can run my analyses (more easily).
However, if an item has rows with and rows without a value for closed/target date, I get multiple rows like so:
I don't want it to include those rows with multiple options. It should just keep the one with a value (if available).
The row with the most recent date should always contain the complete and most accurate data.
I tried to use CALCULATETABLE in combination with BLANK(), but that also filter out items where none of the rows are filled while those are correct and should stay included.
Is there an easy way to correct this, other than not including those columns or replacing them with measures?
Sample table:
| Work item ID | State Category | Created Date | Closed Date | Target Date | Date |
| 1 | Proposed | 1-1-2024 | 4-1-2024 | 3-1-2024 | 1-1-2024 |
| 1 | In Progress | 1-1-2024 | 4-1-2024 | 3-1-2024 | 2-1-2024 |
| 1 | In Progress | 1-1-2024 | 4-1-2024 | 3-1-2024 | 3-1-2024 |
| 1 | Completed | 1-1-2024 | 4-1-2024 | 3-1-2024 | 4-1-2024 |
| 2 | Proposed | 1-1-2024 | 1-1-2024 | ||
| 2 | In Progress | 1-1-2024 | 2-1-2024 | ||
| 2 | Completed | 1-1-2024 | 3-1-2024 | ||
| 2 | Completed | 1-1-2024 | 4-1-2024 | ||
| 3 | Proposed | 1-1-2024 | 2-1-2024 | 1-1-2024 | |
| 3 | In Progress | 1-1-2024 | 2-1-2024 | 2-1-2024 | |
| 3 | In Progress | 1-1-2024 | 2-1-2024 | 3-1-2024 | |
| 3 | In Progress | 1-1-2024 | 2-1-2024 | 4-1-2024 | |
| 4 | Proposed | 1-1-2024 | 1-1-2024 | ||
| 4 | Completed | 1-1-2024 | 2-1-2024 | 5-1-2024 | 2-1-2024 |
| 5 | Proposed | 1-1-2024 | 1-1-2024 | ||
| 5 | Proposed | 1-1-2024 | 2-1-2024 | ||
| 5 | In Progress | 1-1-2024 | 3-1-2024 | ||
| 5 | Completed | 1-1-2024 | 4-1-2024 | 2-1-2024 | 4-1-2024 |
The easiest way is to do the transformation in Power Query.
Here is a sample code using your example data. You can paste this code into the advanced editor of a blank query and look through the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooyi/IL05NATINdQ11jQyMTIBMEwTTGMGEK4jVgWj2zFMA6k8vSi0uJka/EYX6jdH1O+fnFuSklhDnehNk3UY4va4AxYboynE6FqbDCF0HDufB1BuTqB7FA8Z4PWCEI9KMCfnDCEdskaLRmFyNKB40IS2GTHCHH5INpji8Z0qaZYSVY5hOMPUYo+sgnLixBl0sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work item ID" = _t, #"State Category" = _t, #"Created Date" = _t, #"Closed Date" = _t, #"Target Date" = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work item ID", Int64.Type}, {"State Category", type text}, {"Created Date", type date}, {"Closed Date", type date}, {"Target Date", type date}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Work item ID"}, {{"_nestedTable", each _, type table [Work item ID=nullable number, State Category=nullable text, Created Date=nullable date, Closed Date=nullable date, Target Date=nullable date, Date=nullable date]}}), Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.SelectRows(_, (x)=> x[Date] = List.Max([Date])), type table [Work item ID=nullable number, State Category=nullable text, Created Date=nullable date, Closed Date=nullable date, Target Date=nullable date, Date=nullable date]}}), #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"State Category", "Created Date", "Closed Date", "Target Date", "Date"}, {"State Category", "Created Date", "Closed Date", "Target Date", "Date"}) in #"Expanded _nestedTable"
1 Reply
jgeddesSuper User
The easiest way is to do the transformation in Power Query.
Here is a sample code using your example data. You can paste this code into the advanced editor of a blank query and look through the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooyi/IL05NATINdQ11jQyMTIBMEwTTGMGEK4jVgWj2zFMA6k8vSi0uJka/EYX6jdH1O+fnFuSklhDnehNk3UY4va4AxYboynE6FqbDCF0HDufB1BuTqB7FA8Z4PWCEI9KMCfnDCEdskaLRmFyNKB40IS2GTHCHH5INpji8Z0qaZYSVY5hOMPUYo+sgnLixBl0sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work item ID" = _t, #"State Category" = _t, #"Created Date" = _t, #"Closed Date" = _t, #"Target Date" = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Work item ID", Int64.Type}, {"State Category", type text}, {"Created Date", type date}, {"Closed Date", type date}, {"Target Date", type date}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Work item ID"}, {{"_nestedTable", each _, type table [Work item ID=nullable number, State Category=nullable text, Created Date=nullable date, Closed Date=nullable date, Target Date=nullable date, Date=nullable date]}}), Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.SelectRows(_, (x)=> x[Date] = List.Max([Date])), type table [Work item ID=nullable number, State Category=nullable text, Created Date=nullable date, Closed Date=nullable date, Target Date=nullable date, Date=nullable date]}}), #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"State Category", "Created Date", "Closed Date", "Target Date", "Date"}, {"State Category", "Created Date", "Closed Date", "Target Date", "Date"}) in #"Expanded _nestedTable"