Forum Discussion

BlueNote's avatar
BlueNote
Frequent Visitor
5 years ago
Solved

Add new rows based on most recent Date change

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 NameDate Request FilledReason Code
John DoeJan-201- New Hire
John DoeAug-202- 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.

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

1 Reply

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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