Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Grogu69
Frequent Visitor

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 : 

 

idticket_idgroupcreatedatecloturedateassignation
46785 TK18714 nullnullnullmember1
36745TK18714nullnullnullnull
14989TK18714Groupe1 nullnullmember4
26166TK18714null2024-01-12 nullmember6
64515TK18714nullnull2024-01-18 member2 
1215TK61813nullnullnullnull
1849TK61813Groupe7nullnullmember3
14554TK61813nullnull2023-12-01null
12495TK61813null2023-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_idgroupcreatedatecloturedateassignation
64515 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 ?

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @Grogu69 ,

 

How about this maybe? 🙂

 

Before:

tackytechtom_1-1708029100179.png

 

After:

tackytechtom_0-1708029060198.png


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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

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

 

Result:

dufoq3_0-1709406950869.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

v-cgao-msft
Community Support
Community Support

Hi @Grogu69 ,

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

vcgaomsft_1-1708310792591.png

vcgaomsft_0-1708310771271.png

vcgaomsft_2-1708310891601.png

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
Super User
Super User

Hi @Grogu69 ,

 

How about this maybe? 🙂

 

Before:

tackytechtom_1-1708029100179.png

 

After:

tackytechtom_0-1708029060198.png


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/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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 :):)

 

1849TK61813Groupe7nullnullmember3
14554TK61813nullnull2023-12-01null
12495TK61813null2023-11-08nullmember1

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors