Hi everyone, I need a little help please, I'm stuck on a little problem : I have a table looking like this : idticket_idgroupcreatedatecloturedateassignation46785 TK18714 nullnullnullmember136745TK18714nullnullnullnull14989TK18714Groupe1 nullnullmember426166TK18714null2024-01-12 nullmember664515TK18714nullnull2024-01-18 member2 1215TK61813nullnullnullnull1849TK61813Groupe7nullnullmember314554TK61813nullnull2023-12-01null12495TK61813null2023-11-08nullmember1 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 : idticket_idgroupcreatedatecloturedateassignation64515 TK18714 Groupe1 2024-01-12 2024-01-18 member2 14554TK61813Groupe72023-11-082023-12-01member1 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 ?

Forum Discussion

Grogu69

Frequent Visitor

2 years ago

Solved

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

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 ?

Need Help

tackytechtom

2 years ago

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/

4 Replies

tackytechtom

Most Valuable Professional

2 years ago

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
Frequent Visitor
2 years ago
Thank you very much, it works perfectly 🙂
I didn't thought on this way to do it

Thanks also to Anonymous 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

Anonymous

Not applicable

2 years ago

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

dufoq3

Community Champion

2 years ago

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

Forum Discussion

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

4 Replies

Related content

Circular dependency when sorting a column by another column

Filter depending on another filter

Sort by another column

Circular dependency

The query referenced column ****** which depends on another column