Data Grouping with Min and Max values

[Richard_Halsall]

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

Project	Role	Date	Allocated
Project A	Technician 1	05/05/2023	1
Project A	Technician 1	06/05/2023	1
Project A	Technician 1	07/05/2023	1
Project A	Technician 1	08/05/2023	1
Project A	Technician 1	09/05/2023	1
Project A	Technician 1	10/05/2023	1
Project A	Technician 1	11/05/2023	1
Project A	Technician 2	05/05/2023	1
Project A	Technician 2	06/05/2023	1
Project A	Technician 2	07/05/2023	1
Project A	Technician 2	08/05/2023	1
Project A	Technician 2	09/05/2023	1
Project A	Technician 2	10/05/2023	1
Project A	Technician 2	11/05/2023	1
Project A	Technician 2	12/05/2023	1
Project A	Technician 1	12/05/2023	0
Project A	Technician 1	13/05/2023	0
Project A	Technician 1	14/05/2023	0
Project A	Technician 1	15/05/2023	1
Project A	Technician 1	16/05/2023	1
Project A	Technician 1	17/05/2023	1
Project A	Technician 1	18/05/2023	1
Project A	Technician 1	19/05/2023	1
Project A	Technician 1	20/05/2023	1
Project A	Technician 1	21/05/2023	1

Results Table

Project	Project Phase	Role	Start Date	End Date	Allocated
Project A	Project A Phase 1	Technician 1	05/05/2023	11/05/2023	1
Project A	Project A Phase 1	Technician 2	05/05/2023	12/05/2023	1
Project A	Project A Phase 2	Technician 1	12/05/2023	14/05/2023	0
Project A	Project A Phase 3	Technician 1	15/05/2023	21/05/2023	1

[ChielFaber]

I believe the row numbers for each group (Role) by row could get you what you want.

See: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

To get the min -max value just add a max and min aggregated column in the group by 

[m_dekorte · Accepted solution]

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!