Forum Discussion

x-File's avatar
x-File
Helper I
3 years ago

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

Hi all,

 

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

 

Sub ProjectPhaseStart DateEnd DateMax End Date
Project XGroup 101/08/202301/11/202301/11/2023
Project XGroup 201/08/202301/11/202301/11/2023
Project XGroup 301/06/202301/10/202301/11/2023
Project XGroup 401/07/202301/09/202301/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 ProjectPhaseStart DateEnd DateMax End Date
Project XGroup 101/08/202301/11/202301/11/2023
Project XGroup 201/08/202301/11/2023blank
Project XGroup 301/06/202301/10/2023blank
Project XGroup 401/07/202301/09/2023blank


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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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"