Date before the latest

Hi Anonymous ,

Please check the following steps as below.

 1. Filter the table as below based on COLLECTED  DATE column in power query.

2. Group the table by Order.

3. Insert a custom column as below.

4. After that, Merge the table based on Order and index column from both tables as the screen shot following.

5. Expand the due date column as we need and deleted the uncessary columns to get the excepted reuslt.

For more details, please check the M code as below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdNLDoJAEATQu8waw1TPj7mAiRsvQLj/NUyUtKVWx10TXoCpavY9XW/3C3LGyGlJwFpWy3l+z+lYfmkl6rNJ2oj6XCTtRH2ukg6iPjdJN6I+d0knUZ8HUzPY6z4yYboINFhDRkbaWJtM7dSnKTKu6IlVJkaaVwFNhkaatwH9X268EPDutkDzTsDrm3ES7w5ziIy6+0QN4r2GoF3XvI5mQbuueSOtBO0+9Wlq0C6bFnTKpusmaxnqy4Y+9Vvz72KbPrVrzny1Sac+Hg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"COLLECTED DATE" = _t, #"DUE DATE" = _t, instalment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"COLLECTED DATE", type date}, {"DUE DATE", type date}, {"instalment", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([COLLECTED DATE] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order"}, {{"max due date", each List.Max([DUE DATE]), type date}, {"min collect date", each List.Min([COLLECTED DATE]), type date}, {"instalment", each List.Max([instalment]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [instalment]-1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Order", "Custom"}, Table, {"Order", "instalment"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"DUE DATE"}, {"Table.DUE DATE"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table",{"instalment", "Custom"})
in
    #"Removed Columns"