Forum Discussion

PJVisscher89's avatar
PJVisscher89
Frequent Visitor
2 years ago
Solved

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 summarisec...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    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"
jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
2 years ago

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"