Group column and show other columns values depending on max value on another column

[Grogu69]

Hi everyone, I need a little help please, I'm stuck on a little problem : 

I have a table looking like this : 

id	ticket_id	group	createdate	cloturedate	assignation
46785	TK18714	null	null	null	member1
36745	TK18714	null	null	null	null
14989	TK18714	Groupe1	null	null	member4
26166	TK18714	null	2024-01-12	null	member6
64515	TK18714	null	null	2024-01-18	member2
1215	TK61813	null	null	null	null
1849	TK61813	Groupe7	null	null	member3
14554	TK61813	null	null	2023-12-01	null
12495	TK61813	null	2023-11-08	null	member1

I want to get, for each ticket_id, the max value (not null) of each column (group, createdate, cloturedate, assignation) having the max value in "id" column

So the result would be : 

id	ticket_id	group	createdate	cloturedate	assignation
64515	TK18714	Groupe1	2024-01-12	2024-01-18	member2
14554	TK61813	Groupe7	2023-11-08	2023-12-01	member1

I have no problem to group ticket_id depending select "max" for each other column, but I dont know how to get these values depending on the max "id" value and how to manage the "null"

Can you help me please ?

[dufoq3]

Hi @Grogu69, your assignment is not clear but try this. It should be dynamic.

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBCsMgEEWvIq4j+HUcxxN00W13IZuCy9AS6P0rJkhiBBfC/Pfn6Txr4ihB6Um/npAIKrd21ry+8wa9TLP2HCmMY3UOSpIu88f2+X0zVFdHNe4YzH2ds46MhYFTZ4IrwRQwEmiQqEYUvjq5g2AI/F1ZKF3Gu3HshP3xvhBoUFbW+yJcDFqto3Rbu8dgrHRfu/wB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, ticket_id = _t, group = _t, createdate = _t, cloturedate = _t, assignation = _t]),
    ReplacedValue = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue, Table.ColumnNames(Source)),
    ChangedType = Table.TransformColumnTypes(ReplacedValue,{{"id", Int64.Type}, {"createdate", type date}, {"cloturedate", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"ticket_id"}, List.Transform(List.RemoveItems(Table.ColumnNames(ChangedType), {"ticket_id"}), (colName)=>
      { colName, each Record.FieldOrDefault(Table.FillUp(Table.Sort(_, {{"id", Order.Descending}}), {colName}){0}, colName, null) } ))
in
    GroupedRows

[v-cgao-msft]

Hi @Grogu69 ，

Please do some cleansing of the data first, such as Trim, etc., and then group by [ticket_id].

Advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBNDsIgEIXvwroNfcMwDCdw4dZd040Jy0bTxPt4Fk+mQsX+JSxI5n2Pb+h7wxLUv56mMZczNIDzvZ4xjdc0wQxNb5wE9v/gMpbn4KhxNT9Nt8c9YVFZ+jjnSSCy7aOOuO3Qgn5UQSQjwh5HCpXSTBXk25C9aGYECrfXVo6rcbEOG2c37+g9H5SBLOzHwtVW4rh7FbBaU4vPHd4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" id" = _t, ticket_id = _t, group = _t, createdate = _t, cloturedate = _t, assignation = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{" id", Int64.Type}, {"ticket_id", type text}, {"group", type text}, {"createdate", type date}, {"cloturedate", type date}, {"assignation", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"ticket_id", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"ticket_id"}, {{"id", each List.Max([#" id"]), type nullable number}, {"group", each List.Max([group]), type nullable text}, {"creartedate", each List.Max([createdate]), type nullable date}, {"cloturedate", each List.Max([cloturedate]), type nullable date}, {"assignation", each List.Max([assignation]), type nullable text}})
in
    #"Grouped Rows"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[tackytechtom · Accepted solution]

Hi @Grogu69 ,

How about this maybe? 🙂

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBCsMgEEWvIq4j+HUcxxN00W13IZuCy9AS6P0rJkhiBBfC/Pfn6Txr4ihB6Um/npAIKrd21ry+8wa9TLP2HCmMY3UOSpIu88f2+X0zVFdHNe4YzH2ds46MhYFTZ4IrwRQwEmiQqEYUvjq5g2AI/F1ZKF3Gu3HshP3xvhBoUFbW+yJcDFqto3Rbu8dgrHRfu/wB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, ticket_id = _t, group = _t, createdate = _t, cloturedate = _t, assignation = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"group", "createdate", "cloturedate", "assignation"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"id", Int64.Type}, {"ticket_id", type text}, {"group", type text}, {"createdate", type date}, {"cloturedate", type date}, {"assignation", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"id", "ticket_id"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"ticket_id", "Attribute", "Value"}, {{"MaxID", each List.Max([id]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"ticket_id", Order.Ascending}, {"MaxID", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"assignation", "group", "createdate", "cloturedate"}),
    #"Grouped Rows1" = Table.Group(#"Filled Down", {"ticket_id"}, {{"ID", each List.Max([MaxID]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Filled Down", {"ticket_id", "MaxID"}, #"Grouped Rows1", {"ticket_id", "ID"}, "Grouped Rows1", JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Grouped Rows1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ticket_id", "MaxID", "group", "createdate", "cloturedate", "assignation"})
in
    #"Reordered Columns"

Lete me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Grogu69]

Thank you very much, it works perfectly 🙂

I didn't thought on this way to do it

Thanks also to @v-cgao-msft but it's not exactly what I need, i don't want just the max value on each column but the max value depending of the max ID value

So with my example you got "member3" for the ticket TK61813, but 12495 is a biggest "id" value than "1849",  so it should return "member1". Thanks for your work anyway :):)

1849	TK61813	Groupe7	null	null	member3
14554	TK61813	null	null	2023-12-01	null
12495	TK61813	null	2023-11-08	null	member1