@Csalinas144 In my view , your data are still inconsistent. Here is a code. I still can't get and explain your "3" in step B of project 101.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdG9CoQwEATgV5HUCtn8qK0eWFjbiYWC5d37l2aUlQu6WywZyEcWJvNsyFpTGl+N6y+dXRpKM+3rt6D7YinfoGOIEAXYIzNEIFJkYIlA0nLIyBKBpO2fNDVLBBcU2bBE8PZPogoXpZJcrdGsJv3VrKhYDfv2Jh9FNZrMi7ISfTSl06wqChptmbbXT510OQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [project = _t, start = _t, step = _t, action = _t, team = _t, completed = _t]),
types = Table.TransformColumnTypes(Source,{{"completed", type date}, {"start", type date}}),
gr_project = Table.Group(types, {"project"}, {{"gr_project", each _}}),
// project processing function
fx_project = (w as table) as table =>
let
group = Table.Group(w, {"start", "step"}, {{"max_completed", each List.Max([completed]), type nullable date}, {"min_completed", each List.Min([completed]), type nullable date}, {"all", each _, type table [project=nullable text, start=nullable date, step=nullable text, action=nullable text, team=nullable text, completed=nullable date]}}),
index = Table.AddIndexColumn(group, "idx", 0, 1, Int64.Type),
// function for teams
fx_ta_teams = (x as table, d as date) as table =>
[a = Table.DuplicateColumn(x, "completed", "ta_teams"),
b = Table.TransformColumns(a, {"ta_teams", (x) => Duration.Days(x - d) })][b],
ta_step =
Table.AddColumn(
index,
"ta_step",
(x) =>
if x[idx] = 0
then Duration.Days(x[max_completed] - x[min_completed])
else Duration.Days(x[max_completed] - index[max_completed]{x[idx] - 1})
),
ta_teams =
Table.AddColumn(
ta_step,
"all_teams",
(x) =>
if x[idx] = 0
then fx_ta_teams(x[all], x[start])
else fx_ta_teams(x[all], ta_step[max_completed]{x[idx] - 1})
),
cols = Table.SelectColumns(ta_teams,{"ta_step", "all_teams"}),
expand = Table.ExpandTableColumn(cols, "all_teams", {"project", "start", "step", "action", "team", "completed", "ta_teams"}),
reorder = Table.ReorderColumns(expand,{"project", "start", "step", "action", "team", "completed", "ta_step", "ta_teams"})
in
reorder,
project_processing = Table.TransformColumns(gr_project, {"gr_project", fx_project}),
combine = Table.Combine(project_processing[gr_project])
in
combine
