Add new rows based on most recent Date change

[BlueNote]

Hi Everyone,

This is something that is far beyond my current PQ abilities.

I need to get a table that looks like this (original table):

Employee Name	Date Request Filled	Reason Code
John Doe	Jan-20	1- New Hire
John Doe	Aug-20	2- Job Change

To look like this (Tran:

Employee Name	Date	Most Recent Reason Code
John Doe	Jan-20	1- New Hire
John Doe	Feb-20	1- New Hire
John Doe	Mar-20	1- New Hire
John Doe	Apr-20	1- New Hire
John Doe	May-20	1- New Hire
John Doe	Jun-20	1- New Hire
John Doe	Jul-20	1- New Hire
John Doe	Aug-20	2- Job Change
John Doe	Sep-20	2- Job Change
John Doe	Oct-20	2- Job Change
John Doe	Nov-20	2- Job Change
John Doe	Dec-20	2- Job Change
John Doe	Jan-21	2- Job Change
John Doe	Feb-21	2- Job Change

So basically the logic is: for each row in the original table find the next row with the next latest date and the same employee name and insert new rows for the months between the dates using the same employee name and reason code. This needs to iterate on all rows until a user specified final date (Feb 2021 in this examle) is reached.

Thanks for any help.

[Jimmy801 · Accepted solution]

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