Recursive Function - Repeating a Block of M

[Paul_Rockliffe]

I'm after some help with a tricky task I'm trying to complete.  I have a series of rows of data where I need to calculate a date based on some other information.  I have a date for the first row, from there I can run a merge and some transformations to calculate the date for some of the other rows.  When I have those dates I can rerun the merge and transformations to calculate the date for some mre rows, then that pattern repeats until I have dates for every row.  Each row is dependent on another row, so looping over my process will result in a complete column of dates.

How do I construct this as a function so that my Power Query M merge and transform runs as many times as it needs to to completely fill my date column?

[AntrikshSharma]

@Paul_Rockliffe You can use List.Generate, List.Accumulate, or recursive functions using "@" symbol on the custom function, upload your sample in google drive/onedrive and share the link.

[Paul_Rockliffe]

Thanks, I can't easily share a sample file, but I've been working through my first loop making sure that all works and have it producing the correct answers, so hopefully you can advise me based on the code lifted from the Advanced Editor.  I know you won't be able to give me the exact code, but I sort of understand what I'm trying to do, just not where to start, so I might be OK with some pointers or a generic solution.   

If you need more info I can try to get a suitable sample tomorrow.

The M is below, to explain what I'm doing, there are a few steps that prepare the result of another query, then a block of transformations that I want to loop over, then some steps that tidy up the result.  

The data is the underlying data behind a Gantt chart, I am trying to build dynamic start dates based on dependencies and the finish date of other tasks in the table.  In the initial state the Start Date column contains a start date against any task that begins when the Project starts.  The table contains Task IDs for each Task and also the IDs of tasks that must be completed first and those that can be completed at the same time.

The block of M that I want to loop is doing two self-joins that ultimately cascade new values into the Start Date column, based on Start Data + Duration for preceeding Tasks.  This all works really well, but it finds Start Dates only for the next level down in the task hierarchy.  A second loop will find the next level down, then each subsequent loop fills in the next level until there are no null values left in the Start Date column.

I hope that makes enough sense!  Hopefully it doesn't matter too much what the block of M is doing and there's an easy syntax for wrapping it in a loop and exiting the loop when there are no null values.

This is really at the top end of my experience, so really hoping someone can help me out!

Thanks!

let
    Source = #"Task List - Build Start and End Dates",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Task ID", "Duration", "Earliest Start Date", "Start With", "Start After"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns",{{"Earliest Start Date", "Start Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Start Date", type date}}),
    
//Loop this element of the process
    
    #"Self-join on Start With" = Table.NestedJoin(#"Changed Type1", {"Start With"}, #"Changed Type1", {"Task ID"}, "Removed Other Columns", JoinKind.LeftOuter),
    #"Self-join on Start After" = Table.NestedJoin(#"Self-join on Start With", {"Start After"}, #"Self-join on Start With", {"Task ID"}, "Self-join on Start With", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Self-join on Start After",{{"Removed Other Columns", "Start With Dates"}, {"Self-join on Start With", "Start After Dates"}}),
    #"Sort on Task ID" = Table.Sort(#"Renamed Columns",{{"Task ID", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sort on Task ID",{"Task ID", "Duration", "Start Date", "Start With", "Start After", "Start With Dates", "Start After Dates"}),
    #"Expanded Start With Dates" = Table.ExpandTableColumn(#"Reordered Columns", "Start With Dates", {"Start Date"}, {"Start With Dates.Start Date"}),
    #"Expanded Start After Dates" = Table.ExpandTableColumn(#"Expanded Start With Dates", "Start After Dates", {"Duration", "Start Date"}, {"Start After Dates.Duration", "Start After Dates.Start Date"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Start After Dates",{{"Start With Dates.Start Date", "Start With Date"}, {"Start After Dates.Start Date", "Start After Date"}, {"Start After Dates.Duration", "Start After Duration"}}),
    #"Conditionally Create Start After Dates" = Table.ReplaceValue(
        #"Renamed Columns2",
        each [Start After Date],
        each if [Start After Date] = null then null else Date.AddDays([Start After Date], [Start After Duration]),
        Replacer.ReplaceValue,{"Start After Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Conditionally Create Start After Dates",{{"Start With Date", type date}, {"Start After Date", type date}}),
    
    #"Coalesce Max Start Date" = Table.ReplaceValue(#"Changed Type",
    each [Start Date],
    each List.Max({[Start Date], [Start With Date], [Start After Date]}) ,
    Replacer.ReplaceValue,{"Start Date"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Coalesce Max Start Date",{{"Start Date", type date}}),

 // End the loop here.  The test is if the "Start Date" column contains any null values, do another loop, otherwise carry on with the M below.

    #"Create End Date" = Table.AddColumn(#"Changed Type3", "End Date", each Date.AddDays([Start Date], [Duration])),
    #"Reordered Columns1" = Table.ReorderColumns(#"Create End Date",{"Task ID", "Duration", "Start Date", "End Date", "Start With", "Start After", "Start With Date", "Start After Duration", "Start After Date"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"End Date", type date}})
in
    #"Changed Type2"