Forum Discussion

Richard_Halsall's avatar
Richard_Halsall
Helper IV
3 years ago
Solved

Data Grouping with Min and Max values

Hi

I am trying to achieve the results table from the data table, in summary I need to group each role by allocated and calculate min and max dates and create a new field called Project Phase

 

Can anybody help please? Thanks

 

Data Table

ProjectRoleDateAllocated
Project ATechnician 105/05/20231
Project ATechnician 106/05/20231
Project ATechnician 107/05/20231
Project ATechnician 108/05/20231
Project ATechnician 109/05/20231
Project ATechnician 110/05/20231
Project ATechnician 111/05/20231
Project ATechnician 205/05/20231
Project ATechnician 206/05/20231
Project ATechnician 207/05/20231
Project ATechnician 208/05/20231
Project ATechnician 209/05/20231
Project ATechnician 210/05/20231
Project ATechnician 211/05/20231
Project ATechnician 212/05/20231
Project ATechnician 112/05/20230
Project ATechnician 113/05/20230
Project ATechnician 114/05/20230
Project ATechnician 115/05/20231
Project ATechnician 116/05/20231
Project ATechnician 117/05/20231
Project ATechnician 118/05/20231
Project ATechnician 119/05/20231
Project ATechnician 120/05/20231
Project ATechnician 121/05/20231

 

Results Table

ProjectProject PhaseRoleStart DateEnd DateAllocated
Project AProject A Phase 1Technician 105/05/202311/05/20231
Project AProject A Phase 1Technician 205/05/202312/05/20231
Project AProject A Phase 2Technician 112/05/202314/05/20230
Project AProject A Phase 3Technician 115/05/202321/05/20231



Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    Hi Richard_Halsall 

     

    See if this works for you, copy the full script into a new blank query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldO7CoNAGEThV5GtFXbGS0yZN0iRTixkEdRCQfL+xNI0Jqfbha86P9N14blvy5je2SPk4TWmaZ3TPKyZjm9d1IWjy+Op0OeXtgH2BmwL7B1YRYL1JzaoZlDNoJpBNYNqJtVMq8nkHiccf+KS4IpgMhCRhYhMRGQjIiMxGYm/z91/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Role = _t, Date = _t, Allocated = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Allocated", Int64.Type}, {"Date", type date}}, "en-NL"),
    GroupRows = Table.Group(ChType, {"Project", "Role", "Allocated"}, {{"Min Date", each List.Min([Date]), type nullable text}, {"Max Date", each List.Max([Date]), type nullable text}}, GroupKind.Local),
    AddPhase = Table.Group(GroupRows, {"Project"}, {{"t", each Table.AddColumn( _, "Phase", (x)=> "Phase" & Text.From( List.PositionOf( List.Distinct([Min Date]), x[Min Date])+1 )), type table [Project=nullable text, Phase= nullable text, Role=nullable text, Allocated=nullable number, Min Date=nullable text, Max Date=nullable text]}}),
    Expand = Table.ExpandTableColumn(AddPhase, "t", {"Phase", "Role", "Allocated", "Min Date", "Max Date"}, {"Phase", "Role", "Allocated", "Min Date", "Max Date"})
in
    Expand

     

    with this result

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

2 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi Richard_Halsall 

     

    See if this works for you, copy the full script into a new blank query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldO7CoNAGEThV5GtFXbGS0yZN0iRTixkEdRCQfL+xNI0Jqfbha86P9N14blvy5je2SPk4TWmaZ3TPKyZjm9d1IWjy+Op0OeXtgH2BmwL7B1YRYL1JzaoZlDNoJpBNYNqJtVMq8nkHiccf+KS4IpgMhCRhYhMRGQjIiMxGYm/z91/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Role = _t, Date = _t, Allocated = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Allocated", Int64.Type}, {"Date", type date}}, "en-NL"),
    GroupRows = Table.Group(ChType, {"Project", "Role", "Allocated"}, {{"Min Date", each List.Min([Date]), type nullable text}, {"Max Date", each List.Max([Date]), type nullable text}}, GroupKind.Local),
    AddPhase = Table.Group(GroupRows, {"Project"}, {{"t", each Table.AddColumn( _, "Phase", (x)=> "Phase" & Text.From( List.PositionOf( List.Distinct([Min Date]), x[Min Date])+1 )), type table [Project=nullable text, Phase= nullable text, Role=nullable text, Allocated=nullable number, Min Date=nullable text, Max Date=nullable text]}}),
    Expand = Table.ExpandTableColumn(AddPhase, "t", {"Phase", "Role", "Allocated", "Min Date", "Max Date"}, {"Phase", "Role", "Allocated", "Min Date", "Max Date"})
in
    Expand

     

    with this result

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!