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/

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity 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