Forum Discussion

Grogu69's avatar
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...
Need Help
  • tackytechtom's avatar
    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/

Anonymous's avatar
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