Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Level up your Power BI skills this month - build one visual each week and tell better stories with data! Get started

Reply
PJVisscher89
Frequent Visitor

Summarise Columns should not create multiple rows per unique ID

‎06-19-2024 06:44 AM

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:

PJVisscher89_0-1718804257628.png

 

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 IDState CategoryCreated DateClosed DateTarget DateDate
1Proposed1-1-20244-1-20243-1-20241-1-2024
1In Progress1-1-20244-1-20243-1-20242-1-2024
1In Progress1-1-20244-1-20243-1-20243-1-2024
1Completed1-1-20244-1-20243-1-20244-1-2024
2Proposed1-1-2024  1-1-2024
2In Progress1-1-2024  2-1-2024
2Completed1-1-2024  3-1-2024
2Completed1-1-2024  4-1-2024
3Proposed1-1-2024 2-1-20241-1-2024
3In Progress1-1-2024 2-1-20242-1-2024
3In Progress1-1-2024 2-1-20243-1-2024
3In Progress1-1-2024 2-1-20244-1-2024
4Proposed1-1-2024  1-1-2024
4Completed1-1-20242-1-20245-1-20242-1-2024
5Proposed1-1-2024  1-1-2024
5Proposed1-1-2024  2-1-2024
5In Progress1-1-2024  3-1-2024
5Completed1-1-20244-1-20242-1-20244-1-2024

 

 

 

Labels:
Message 1 of 2
279 Views
0
1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

‎06-19-2024 07:00 AM

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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Message 2 of 2
261 Views
0
1 REPLY 1
jgeddes
Super User
Super User

‎06-19-2024 07:00 AM

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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Message 2 of 2
262 Views
0

Helpful resources

Announcements
April Power BI Update Carousel

Power BI Monthly Update - April 2026

Check out the April 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All
Users online (5,900)