One max end date per project even if there are multiple rows with the same max end date

[x-File]

Hi all,

Suppose I have a table like the following, with multiple sub projects (all with a unique title): 

Sub Project	Phase	Start Date	End Date	Max End Date
Project X	Group 1	01/08/2023	01/11/2023	01/11/2023
Project X	Group 2	01/08/2023	01/11/2023	01/11/2023
Project X	Group 3	01/06/2023	01/10/2023	01/11/2023
Project X	Group 4	01/07/2023	01/09/2023	01/11/2023

When doing some formula in Power Query, I can get the Min and Max Date preatty easily per project, but it will then apply that to all rows - which is not what I want, I want it to apply to just one row per sub project, which I can do as well, but it goes wrong when I have two groups from the same project that have exaclty identical Sub Project en Start or End dates. In this case, Group 2 and 3 from Projext X are exactly identical in their timelines.

What I want is this:

Sub Project	Phase	Start Date	End Date	Max End Date
Project X	Group 1	01/08/2023	01/11/2023	01/11/2023
Project X	Group 2	01/08/2023	01/11/2023	blank
Project X	Group 3	01/06/2023	01/10/2023	blank
Project X	Group 4	01/07/2023	01/09/2023	blank

I already grouped my table by sub project, and that worked fine so every sub project had just one row with start en end dates, however when I merged it back with the original table, it apllied to two rows.

If you people could help me, I'd be very very very happy!

best,

Felix

[Vijay_A_Verma]

Whatever I could understand, check the below sample code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlGIUNJRci/KLy1QMASyDPUt9I0MjIzBTENDCDtWB5tyI9KUQ9SYISk3wKfcBKzGHKHcElN1JG6nGxnhU06U0yNxO93IFKo8FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sub Project" = _t, Phase = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sub Project", type text}, {"Phase", type text}, {"Start Date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"Sub Project"}, {{"All", each Table.FromColumns(Table.ToColumns(_) & {{List.Max(_[End Date])} & List.Repeat({null}, Table.RowCount(_)-1)}, Table.ColumnNames(_) & {"Max End Date"})}})[All])
in
    #"Grouped Rows"