Add new rows based on most recent Date change

Hello BlueNote 

try this solution. It groups first the data by name... because I suppose you will have more then 1 name in your table. Afterwards I add a Index column to the grouped table and add new column that creates a list of dates of every month till the next event. If no event is found, the last month is used. Here the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9V0lHySszTNTIAMgx1FfxSyxU8MotSlWJ1UNQ4lqZD1BjpKnjlJyk4ZyTmpQNVxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Date Request Filled" = _t, #"Reason Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Request Filled", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name"}, {{"AllRows", each 
    let 
        AddIndexAndSort = Table.AddIndexColumn(Table.Sort(_, {{"Date Request Filled", Order.Ascending}}), "Index", 1,1),
        AddListWithDates = Table.AddColumn
        (
            AddIndexAndSort,
            "Date Request Filled New",
            (add)=> let 
                GetEndDate = try Table.SelectRows(AddIndexAndSort, each [Index]= add[Index]+1)[Date Request Filled]{0} otherwise Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())),
                GetListOfDates = List.Transform(List.Numbers(0,((Date.Year(GetEndDate)-Date.Year(add[Date Request Filled]))*12)+ (Date.Month(GetEndDate)-Date.Month(add[Date Request Filled])) ,1), each Date.AddMonths(add[Date Request Filled], _))
            in 
             GetListOfDates
        )
        
        
    in 
     AddListWithDates }}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Date Request Filled", "Reason Code", "Date Request Filled New"}, {"Date Request Filled", "Reason Code", "Date Request Filled New"}),
    #"Expanded Date Request Filled New" = Table.ExpandListColumn(#"Expanded AllRows", "Date Request Filled New")
in
    #"Expanded Date Request Filled New"

this is the output

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy