Formulae to copy/paste dates based on conditions

Hi Fab117 ,
if we cannot rely on the correct order and completeness of the rows in each Project, then this would be the secure approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDBCoMwDIZfZXiWksZ1c8dNdthtd/EgTkZBUnAKe/xFumqjCL38zZfma8oyuSZp8iA72HqwjjigUXBRCJhx0EZp8KFKPfzsaiJL76kKc5UDKj4CvX/bZpyePdT0OjSOht51fA9a8WH06EdA5kPoKzr3GftWFtksF+RtbT7BedDBJQQ4NscIzWCD7pnreDnIP9aybzHn+aHo2/RiXmzMUcLxIouVuVntDuW7u+Ywo345RvbJnZ/iCec/Wf0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectId = _t, #"Project Phase" = _t, earliestStartDate = _t, latestDueDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"ProjectId", type text}, 
      {"Project Phase", type text}, 
      {"earliestStartDate", type date}, 
      {"latestDueDate", type date}
    }
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"ProjectId"}, 
    {
      {
        "Partition", 
        each 
          let
            BufferedTable = Table.Buffer(_), 
            AddColumn = Table.AddColumn(
              BufferedTable, 
              "earliestStartDateCorrected", 
              each 
                if [Project Phase] = "Initiation" then
                  [earliestStartDate]
                else if [Project Phase] = "Planning" then
                  Table.SelectRows(BufferedTable, (r) => r[Project Phase] = "Initiation"){0}[
                    latestDueDate
                  ]
                else if [Project Phase] = "Execution and control" then
                  Table.SelectRows(BufferedTable, (r) => r[Project Phase] = "Planning"){0}[
                    latestDueDate
                  ]
                else if [Project Phase] = "Closure" then
                  Table.SelectRows(BufferedTable, (r) => r[Project Phase] = "Execution and control"){
                    0
                  }[latestDueDate]
                else
                  null
            )
          in
            AddColumn
      }
    }
  ), 
  #"Expanded Partition" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Partition", 
    {"Project Phase", "earliestStartDate", "latestDueDate", "earliestStartDateCorrected"}, 
    {"Project Phase", "earliestStartDate", "latestDueDate", "earliestStartDateCorrected"}
  )
in
  #"Expanded Partition"

Replace the code in step "Source" by a reference to your data source.

Hello, Fab117 

let
    Source = your_table,
    f = (tbl as table) =>
        [a = Table.ToColumns(tbl),
        b = List.FirstN(a{2}, 1) & List.FirstN(a{3}, 3),
        out = Table.FromColumns(a & {b}, Table.ColumnNames(tbl) & {"earliestStartDateCorrected"})][out],
    gr = Table.Group(Source, {"ProjectId"}, {{"all", (x) => f(Table.Sort(x, "earliestStartDate"))}}),
    expand = Table.ExpandTableColumn(gr, "all", {"Project Phase", "earliestStartDate", "latestDueDate", "earliestStartDateCorrected"})
in
    expand

Hi Fab117 

You can refer to the following solution.

1.Create a new step after "Changed Type"

 

2.Then input the following code to new step and rename the new step to "Grouped Rows" 

 

= Table.Group(#"Changed Type", {"ProjectId"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})

 

3.Create a new step after "Grouped Rows" and  input the following code to new step and rename the new step to "Expanded Data"

 

= Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Project Phase", "earliestStartDate", "latestDueDate", "Index"}, {"Project Phase", "earliestStartDate", "latestDueDate", "Index"})

 

4.Add a custom column and input the following code

 

if [Index]=1 then [earliestStartDate] else List.Min(
Table.SelectRows(#"Expanded Data",(x)=>x[ProjectId]=[ProjectId] and x[Index]=[Index]-1)[latestDueDate])

 

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.